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 1,474 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
• 13,690 points

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

0 votes
1 answer

EXCEL: How to automatically add serial number in Excel Table using formula that is immune to filtering / sorting?

That's a great question, and it's a ...READ MORE

0 votes
1 answer

EXCEL: How to automatically add serial number in Excel Table using formula that is immune to filtering / sorting?

These techniques can add a running column ...READ MORE

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

0 votes
1 answer

How to sort dates from Oldest to Newest in Excel?

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

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

0 votes
1 answer

How to count distinct values in Excel

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

0 votes
1 answer

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

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

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

0 votes
1 answer

How to enter a series of numbers automatically in Excel

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