Forum Discussion

jenna2605's avatar
jenna2605
Copper Contributor
May 14, 2022

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

BI & Data Analysis
Formulas and Functions
office 365

5 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 14, 2022

    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")

     

    • jenna2605's avatar
      jenna2605
      Copper Contributor
      May 14, 2022

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

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        May 14, 2022

        jenna2605 

        Sorry, didn't think about decimals. As variant

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

         

Resources