Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Number formatting

0

Hello there, I hope you all good!

I need help on formatting numbers for my chart here in the attached excel file. What code I should write in the custom format option to format the number as follow:

100000 = 100k

123000 = 123K

1000000 = 1M

1230000 = 1,2M

There are the example, I have look for the formulas from youtube & google yet still haven't found the right code that meets my need soo I really need your help. Thank you in advance! :D

excel
MS Office Version: Fida_mutia
posted by: Fida_mutia (rep: 28) on Sat Apr 29, 2023 at 1:39 pm
Answer
Discuss

Answers

0
Selected Answer

Hello Fida_mutia and welcome to the forum.

For the Cell(s) you want this formatting, enter the following line in the Custom number format:

[<999950]0.0,"K";[<999950000]0.0,,"M";0.0,,,"B"

If you don't want decimals, simply remove the " .0 " from the line above where you don't want decimals.

I have attached a file so you can see how this works.

Updated April 30/23

Attached second file showing different outcomes from different custom formats.

Also see my April 30th "Discussion" post below.

Hope this helps.

Cheers   :-)

posted by: WillieD24 (rep: 557) on Sat Apr 29, 2023 at 2:42 pm
Discuss

Discussion

Hello Willie,

Thank you for the reply. Well actually the code given was somehow didn't come out as I expected. Maybe because I use different Excel version, I'm using Excel 2019 btw or maybe bcs it uses different delimiter default well I dont know exactly what makes the code didn't work. So then I tried to modify the code from you little bit and this is the code that works for me

[<999950]0."K";[<999950000]0,0.."M";#.#00,,,"B"


However this code round up the 3rd digit like if I type 123456, the code will convert it to 124k and this is not the result i want. Could you please modify the code that wont round up nor round down the numbers?


Many thanks
Fida_mutia (rep: 28) Apr 30, '23 at 9:26 am
Hi Fida,
I used Excel 2016 to provide the solution and have not encountered the rounding issue you describe. Using 2019 should yeild the same results. As you can see in my updated file (attached above), 123456 will become 123K, or 123.5K, or 123.46K (depending on which custom format is used) but not 124K. However, "123789" will become 124K (or 123.8 or 123.79)
When I reviewed your modified version of my suggestion, I noticed that you have some syntax errors - using a " . " (period) when it should be a " , " (comma), and vice-versa.
To have Excel treat "123456" as "123000" to elimate rounding will become extremely complex and require some VBA. 
My updated file shows the different results depending on the custom format chosen. As you can see, " 0 " returns no decimals, " 0.0 " yields one decimal. " 0.00 " yields two decimals; and there is always rounding. Also, Excel will treat "123456" and "123,456" as the same.

Cheers   :-)
WillieD24 (rep: 557) Apr 30, '23 at 12:25 pm
Hi  again Fida. Did Willie's revised solution work for you? (I thought it was very good). If so, please mark his answer as Selected- thanks!
John_Ru (rep: 6142) May 4, '23 at 6:35 am
Hello all sorry I just replied, my works just been so hectic. Anyway thanks Willie, I may will try to fully comprehend your last reply there bcs I actually still dont get the idea but thanks a lot really your first reply was already helpful!

Cheers! :)
Fida_mutia (rep: 28) May 5, '23 at 8:39 am
Understood /thanks Fida (I was surprised at the delay since you'd always responded quickly in the past).

If you have questions on Willie's answer, just ask! 
John_Ru (rep: 6142) May 5, '23 at 9:23 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login