Parse text & numbers & format

[dmiami]

Hi,

thanks for the help, I need to clean up some data in excel,
I need to strip the letter and format the numbers.

i.e. (B=Billion M=Million)

9.965B = 9.965.000
90.965B = 90.965.000
900.965B = 900.965.000
2.1M = 2.100
20.1M = 20.100
200.1M = 200.100

There will always be 3 decimals with the B
ex. 9.965B

There will always be 1 decimal with the M
ex. 2.1M

Also, there is an empty space before the first
number, to the left in the cell.


Thanks in advance

[Excel_Geek]

If all of the values contain either a "B" or an "M", this will work.

=IF(ISERROR(FIND("B",A1)),MID(A1,2,FIND("M",A1)-2)*1000000,MID(A1,2,FIND("B",A1)-2)*1000000000)

If not, some tweaking will be needed.