help please

[Glenn Masterson]

I enter data from the internet by right clicking a cell and clicking refresh.
In one column are the prices of the players which look like this £5.5m

Can someone give me a formula that will get rig of the  and m so I am left with £5.5
Then I need it to be recognize as numbers as I need to add the results up in a different sheet.

Thank you

Glenn

 

[Domenic]

Assuming that A2 contains the data, try...

=SUBSTITUTE(SUBSTITUTE(A2,"Â",""),"m","")+0

 

[rpaulson]

This will "extract" the numeric portion.

=MID(A1,3,(LEN(A1)-3))+0

format cells as needed.

Ross

 

[Glenn Masterson]

Thank you it worked perfectly