Converting Text to Numbers

I've opened CSV data in my spreadsheet that includes text and numbers (the numbers are in currency form e.g £25).

However, Excel does not recognise the numbers as numbers- thus I cannot add/subtract them or use any functions or formula.

I've tried reformating them as numbers (using format cells), used the text to column command and multipled them by 1 using the paste special command...however none of this has worked.

Is there a way of telling Excel when I open the CSV data that certain columns are 'number'?

Alternatively, are there any macro's that would read the 'text' data in a cell and replace it with 'numbers'?