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, 2022 in Database by gaurav
• 23,260 points
1,306 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, 2022 by Edureka
• 13,670 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, 2022 in Database by gaurav
• 23,260 points
633 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
1,272 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, 2022 in Database by gaurav
• 23,260 points
4,723 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, 2022 in Database by Edureka
• 13,670 points
1,763 views
0 votes
1 answer

How to count distinct values in Excel

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

answered Mar 15, 2022 in Database by gaurav
• 23,260 points
1,184 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, 2022 in Database by gaurav
• 23,260 points
1,635 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, 2022 in Database by Edureka
• 13,670 points
2,592 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, 2022 in Database by Edureka
• 13,670 points
573 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP