£1.5m to 1.5

[tony]

Hi
I have a worksheet with column c recording values in the format of £1.5m ,
£2m etc but I need to sum this row is this possible ? or easy to convert ?
Thanks in advance Tony

 

[Guest]

Tony,
I presume that the £ and m indicate that you are dealing with text data,
not numeric data simply formated to show the £ and m.

If it is text data, and assuming your text, e.g. "£1.2m" is in cell A1,
put this formula in cell B1, or other cell.

=VALUE(LEFT(MID(A1,2,33),LEN(A1)-2))

Copy down to match your data in column A, and then add column B.

 

[Guest]

This may work. Assuming your data is in cell C1:C100 (change the range
reference as needed):

=SUM(VALUE(MID(C1:C100,SEARCH(CHAR(163),C1:C100,1)+1,SEARCH("m",C1:C100,1)-SEARCH(CHAR(163),C1:C100,1)-1)))

entered with Control+Shift+Enter

 

[Roger Govier]

Hi Tony

In another column enter
=--SUBSTITUTE(SUBSTITUTE(C1,"£",""),"m","")
Copy down for the range of rows required.
If you want this to be a permanent change, copy the new range and Paste
Special>Values back over the top of the old range.


Regards

Roger Govier