How can I define 36B als 36 Billion?

[jenna2605]

Hi Guys,

I wrote the numbers in my sheet as "36 M" instead of 36.000.000 and "36 B" instead of 36 billion.

How can excel recognize this as numbers?

Thanks in advance

[#2]

@jenna2605 

If return result into the same cell that's with VBA programming. If into another cell that could be formula like

=--SUBSTITUTE(D4, " M", "000000")

[#3]

thanks sergei. But if I substitute M by 000000, "3,4 M" are 3400000 which is one 0 to much? @Sergei Baklan 

[#4]

@jenna2605 

Try

=IF(RIGHT(A1,2)=" M",LEFT(A1,LEN(A1)-2)*1000000,IF(RIGHT(A1,2)=" B",LEFT(A1,LEN(A1)-2)*1000000000,A1))

=WENN(RECHTS(A1;2)=" M";LINKS(A1;LÄNGE(A1)-2)*1000000;WENN(RECHTS(A1;2)=" B";LINKS(A1;LÄNGE(A1)-2)*1000000000;A1))

[#5]

@jenna2605 

Sorry, didn't think about decimals. As variant

=LEFT(A1, FIND(" ",A1) - 1 )*LOOKUP( RIGHT(A1), {"B","M"}, {1e9,1e6} )

[#6]

thank you!