Number Formatting to 10K 5M etc in Excel 2016

0 votes

I want to do something fairly simple in Excel 2016 but seem to be running into a wall. The idea is to convert numbers like this

1 -> 1
10 -> 10
100 -> 100
1000 -> 1K
10000 -> 10K
100000 -> 100K
1000000 -> 1M
....

I got the following number format:

[>=1000000]0,,"M";[>=1000]0,"K";0

But what I get for 20000 is

20000,K

while I was expecting

20K

I think this is fairly simple and I just made a rookie mistake. Anyone?

Thank you! Pat

Apr 4 in Database by gaurav
• 13,560 points
49 views

1 answer to this question.

0 votes
In the format of a user-defined number

[>=1000000]0,,"M";[>=1000]0,"K";0

The thousands delimiter is represented by the comma.

So, if you obtain 2000,K, your thousands delimiter isn't a comma, but rather a different character in your locale settings. For example, the thousands delimiter in German locale is dot, hence the format must be:

[>=1000000]

0.."M";[>=1000]

0."K";0

To be clear, if you modify the thousands delimiter after the format has been applied, the number format will change. However, the initial input must be done with the right thousands delimiter.
answered Apr 4 by Edureka
• 13,640 points

Related Questions In Database

0 votes
1 answer

Convert a number to a letter in C# for use in Microsoft Excel [duplicate]

If you are familiar with using formulas ...READ MORE

answered Feb 23 in Database by gaurav
• 13,560 points
44 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

Query to list number of records in each table in a database

Use the below code to list number of ...READ MORE

answered Oct 11, 2019 in Database by Omaiz
• 560 points
651 views
0 votes
1 answer

How to sort dates from Oldest to Newest in Excel?

Drag down the column to select the ...READ MORE

answered Feb 23 in Database by gaurav
• 13,560 points
48 views
0 votes
0 answers

How to get address, Column Name and Row Name of all marked rows in Excel table as rows in new worksheet

 need the row/column combinations marked with an ...READ MORE

Feb 24 in Database by Edureka
• 13,640 points
88 views
0 votes
1 answer

How to count distinct values in Excel

Use functions to count the number of ...READ MORE

answered Mar 15 in Database by gaurav
• 13,560 points
78 views
0 votes
1 answer

[Excel][VBA] How to draw a line in a graph?

Sub MakeChart() Dim x(20) ...READ MORE

answered Mar 24 in Database by gaurav
• 13,560 points
77 views
0 votes
1 answer

How to transform multiple tables in one excel sheet to one table with Power BI?

If my prediction is correct- You have a ...READ MORE

answered Apr 4 in Database by Edureka
• 13,640 points
91 views
0 votes
1 answer

How to enter a series of numbers automatically in Excel

Excel, unlike other Microsoft Office programmes, does ...READ MORE

answered Apr 4 in Database by Edureka
• 13,640 points
25 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP