Parse text & numbers & format
 

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

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.


--
Excel_Geek


------------------------------------------------------------------------
Excel_Geek's Profile: http://www.excelforum.com/member.php...o&userid=26423
View this thread: http://www.excelforum.com/showthread...hreadid=398976