May 14 2022 01:47 AM
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
May 14 2022 05:38 AM
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")
May 14 2022 05:40 AM
thanks sergei. But if I substitute M by 000000, "3,4 M" are 3400000 which is one 0 to much? @Sergei Baklan
May 14 2022 07:03 AM
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))
May 14 2022 07:07 AM
Sorry, didn't think about decimals. As variant
=LEFT(A1, FIND(" ",A1) - 1 )*LOOKUP( RIGHT(A1), {"B","M"}, {1e9,1e6} )