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 the number is in b5 then try this for size

=IF(ISNUMBER(FIND("B";B5))=TRUE;LEFT(B5;FIND("B";B 5)-1)*1000000;IF(ISNUMBER(FIND("M";B5))=TRUE;LEFT(B5; FIND("M";B5)-1)*1000;B5))

:)


--
nijunge
------------------------------------------------------------------------
nijunge's Profile: http://www.excelforum.com/member.php...o&userid=26404
View this thread: http://www.excelforum.com/showthread...hreadid=398977

Works Great...Thanks for the help!!!!