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,306 views

## 1 answer to this question.

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.
• 13,670 points

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

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

## 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

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

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

## How to sort dates from Oldest to Newest in Excel?

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

## 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

## How to count distinct values in Excel

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

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

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