Excel Symbol

[Guest]

Hello

My problem is when I download data from Accounting Software to Excel.
The data is in British Pounds, and when downloaded to Excel each number has
a symbol attached to it (e.g. ï½£414.90). With this symbol in front of each
number, I am unable to perform forumlas on the data (Excel is not accepting
the data as numerical). I need a way of removing the symbol from each
number.

I can delete the symbol manually from each number, however, it is a long
process.

Is there a function in Excel that removes a symbol?

Any help would be appreciated

Regards,

Bernard Purdon

 

[Bernard Liengme]

Hello Bernard,

Method 1: Find and Replace
First click on a cell with the symbol and in the Formula Bar copy just the
symbol
Select all cells to be fixed (it will not matter if you selected other cells
as well)
Use Edit; Find and Replace; paste you symbol into the Find box and leave the
Replace box empty; use Replace all

Method 2; SUBSTITUTE
Use the same method to get a copy of the symbol
Use formula =--SUBSTITUTE(G16,"?","")
I have used a square root symbol for demo purpose
The double negation converts text to number
You could use Copy followed by Past Special->Values to convert the formula
to actual values and then delete the original column
Do not use =--SUBSTITUTE(G16,"?","£"), this will give text not a number.
Rather format your cells to show £.

Wonder why Excel does not 'see' the £ as currency symbol?
best wishes

 

[Guest]

Thanks Bernard

Method Number 1 did not work......but Method Number 2 did.

FYI......the pound symbol was downloaded from Great Plains
Software.....Excel then added another symbol to each number (square symbol).
When I used the formula from your method 2, the square symbol disappears and
the pound symbol appears.
Excel also recognized the data as numerical (in pounds) and not text ( I can
now perform formulas)


Regards,

Bernard Purdon