How to Change a 1000 Separator to a 100 Separator in Excel

What Is a Thousand Separator Format?

Thousand separator is a symbol (either comma or dot or space etc.) that is used at the 3rd digit of any number. In the USA the character is comma (,), in Germany it is period (.).

The sample dataset (Total Export in 2021) has Product Name and Price with 1000 Separators as column headers.

Method 1 – Using the File Tab to Change a 1000 Separator to a 100 Separator in Excel

Column D (Price with 100 Separator) was inserted.

Steps:

Copy and paste the data from Column C into Column D.

Go to File.

Choose Options.

In Excel Options:

choose Advanced > uncheck Use system separators > enter comma (,) in the Decimal separator box and dot (.) in the Thousands separator box.
Click OK.

Data in both C and D Columns display 100 separators.

Method 2 – Using the Format Cells Dialog Box

To add 100 separators to Column D.

Steps:

Copy and paste the data from Column C into Column D.

Select the cells you want to change and right-click them.
Go to Format Cells.

Note: You may press CTRL + 1 to open the Format Cells dialog box.

In the Format Cells dialog box, go to Number.
In Type, enter the following:

[>=10000]##\,##\,##,00;[>=100]##\,##,00;#,00

This text is used for numbers greater than 9999 and less than 1000000.

Click OK.

Data in Column D displays 100 separators.

Method 3 – Utilizing the TEXT and the REPT Functions

You have a Price with 1000 Separator in Column C and want to get the Price with 100 Separator in Column D.

Enter the following formula in D5.

=TEXT(C5;REPT("##\,";MATCH(LEN(ABS(C5));{3;4;6})-1)&"##")

Press ENTER to get the output.
Drag the Fill Handle.

100 separators will be displayed in Column D.

How to Add 1000 Separator in Excel

Steps:

Copy and paste the data from Column C into Column D.

Select D5:D13.
Right-click the selected cells > go to Format Cells.

In the Format Cells window, choose Number > go to Number > select Use 1000 Separator (.) > change the value of Decimal Places to 0 if you don’t want any decimals > click OK.

The output displays the 1000 separator.

How to Change the Comma Separator in Excel

Steps:

Copy and paste the data from Column C into Column D.

Go to Format Cells by right-clicking the selected cells.

In Number, go to Number > choose any option in Negative Numbers > click OK.

Comma separators were added in Column D.

Things to Remember

When the number is greater than 99 but less than 100,000, all we need to do is type ##\,##. The symbol (\) means that there is text in the following position and the text is a comma (,).
For under 100 format modify the text by adding [>=100]. The text becomes [>=100]##\,##.

Download Practice Workbook

Changing 1000 Separator to 100 Separator.xlsx