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

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.




"tony" wrote:

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

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

"tony" wrote:

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

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
SpecialValues back over the top of the old range.


Regards

Roger Govier


tony wrote:
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