How can I define 36B als 36 Billion?

%3CLINGO-SUB%20id%3D%22lingo-sub-3374406%22%20slang%3D%22en-US%22%3EHow%20can%20I%20define%2036B%20als%2036%20Billion%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3374406%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guys%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wrote%20the%20numbers%20in%20my%20sheet%20as%20%2236%20M%22%20instead%20of%2036.000.000%20and%20%2236%20B%22%20instead%20of%2036%20billion.%3C%2FP%3E%3CP%3EHow%20can%20excel%20recognize%20this%20as%20numbers%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3374406%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3375199%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20define%2036B%20als%2036%20Billion%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3375199%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1390146%22%20target%3D%22_blank%22%3E%40jenna2605%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20return%20result%20into%20the%20same%20cell%20that's%20with%20VBA%20programming.%20If%20into%20another%20cell%20that%20could%20be%20formula%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D--SUBSTITUTE(D4%2C%20%22%20M%22%2C%20%22000000%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3375200%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20define%2036B%20als%2036%20Billion%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3375200%22%20slang%3D%22en-US%22%3E%3CP%3Ethanks%20sergei.%20But%20if%20I%20substitute%20M%20by%20000000%2C%20%223%2C4%20M%22%20are%203400000%20which%20is%20one%200%20to%20much%3F%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3375300%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20define%2036B%20als%2036%20Billion%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3375300%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1390146%22%20target%3D%22_blank%22%3E%40jenna2605%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(RIGHT(A1%2C2)%3D%22%20M%22%2CLEFT(A1%2CLEN(A1)-2)*1000000%2CIF(RIGHT(A1%2C2)%3D%22%20B%22%2CLEFT(A1%2CLEN(A1)-2)*1000000000%2CA1))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DWENN(RECHTS(A1%3B2)%3D%22%20M%22%3BLINKS(A1%3BL%C3%84NGE(A1)-2)*1000000%3BWENN(RECHTS(A1%3B2)%3D%22%20B%22%3BLINKS(A1%3BL%C3%84NGE(A1)-2)*1000000000%3BA1))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3375321%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20define%2036B%20als%2036%20Billion%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3375321%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1390146%22%20target%3D%22_blank%22%3E%40jenna2605%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20didn't%20think%20about%20decimals.%20As%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLEFT(A1%2C%20FIND(%22%20%22%2CA1)%20-%201%20)*LOOKUP(%20RIGHT(A1)%2C%20%7B%22B%22%2C%22M%22%7D%2C%20%7B1e9%2C1e6%7D%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3376204%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20define%2036B%20als%2036%20Billion%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376204%22%20slang%3D%22en-US%22%3Ethank%20you!%3C%2FLINGO-BODY%3E
New Contributor

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

5 Replies

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

 

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

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

@jenna2605 

Sorry, didn't think about decimals. As variant

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

 

thank you!