Separating Numbers and Text

[mikes_r]

Hi,

I have a list of trading volumes that are given as a mix of numbers and letters, i.e. 1.46M, 856.56K. I'm trying to convert the list into the numbers by transforming the 1.46M into 14600000 and 856.56K into 856560, but I've as yet been unsuccessful. Any help would be great.

 

[JoeMo]

Hi,

I have a list of trading volumes that are given as a mix of numbers and letters, i.e. 1.46M, 856.56K. I'm trying to convert the list into the numbers by transforming the 1.46M into 14600000 and 856.56K into 856560, but I've as yet been unsuccessful. Any help would be great.

If you are dealing only with M or K suffixes, this should work:

=IF(RIGHT(A1,1)="M",1000000*LEFT(A1,LEN(A1)-1),1000*LEFT(A1,LEN(A1)-1))

 

[mikes_r]

Thanks, works perfectly.