Forum Discussion
How can I define 36B als 36 Billion?
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
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")
thanks sergei. But if I substitute M by 000000, "3,4 M" are 3400000 which is one 0 to much? SergeiBaklan
Sorry, didn't think about decimals. As variant
=LEFT(A1, FIND(" ",A1) - 1 )*LOOKUP( RIGHT(A1), {"B","M"}, {1e9,1e6} )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))
- thank you!
