Excel Magic Trick 981: 123.45M From Text To Number, 123,450,000 From Number to 123.45M

Topics:
1) Convert Text (123.45M to Number with formula: =SUBSTITUTE(A2,"M","")*1000000, or =LEFT(A2,LEN(A2)-1)*1000000
2) Show Number 123,450,000 as 123.45M with Custom Number Format: 0.00,,\M

[jyoung]

I can't tell you how much I have learned from you and how much time you have saved me. thank you.

[excelisfun]

Haven't heard form you in a while. I am glad that you are still watching! Yes, none of the epic Custom Number Format videos show the back slash, but I have one or two videos that use the TEXT function and the backslash. I learned it from barry houdini at the Mr Excel Message Board. But it is also MS Help for Number Formatting. The Duels will be back this month!! Mr Excel had been writing a lot of books and then I got caught up writing a new book also...

[htothemk]

Thanks a lot. Really useful channel you have here. Thanks a lot for sharing. It's greatly appreciated. Happy Holidays!!

[planiolro]

Excellent video as usually. I know that u did a video dedicated to Custom Number Formatting but I do not remember mentioning anything about introducing characters with the backslash keyboard. There is always something to learn. Microsoft owns you big time since thanks also to you excel has become a friendly program whereas in the past I did not have a clue about. The Excel magic tricks have made the difference.What about the duels? I missing them.

[dubaslopes]

And if I wanted to put the conversion of M * 1, B * 1000 and K / 1000 into the same formula. It's possible?
Thank you

[mohitsinghal]

Another great video. I was facing one small issue; while converting numbers to show M for millions, when I add two commas, it removes 3 zeros for each coma, but as I am from India and as you might be knowing that regional comma style for numbers is different (like 10M is written as 1, 00, 00, 000 or simply 1 crore), how can I write this number as crores as I have to remove 7 zeroes and not a multiple of 3?

[zfh]

There was a challenge how to extract a number from Currency formatted cell: For example, the cell is formatted to GBP and when you type 1000, the cell is shown as GBP 1, 000.00 but what you want 1000 to be extracted as a number and then by using VLookup formula exchange rate is taken from a table which converts GBP 1, 000.00 to USD equivalent. I tried to use FIXED formula to extract the number, but not sure how to identify whether the amount is GBP 1, 000.00 or AUD 1, 000.00. Any thoughts...?

[ronwb]

is SUBSTITUTE found in Excel 2007 and 2010?

[excelisfun]

I am not sure, try THE best place for Excel questions:

mrexcel [dot] com/forum