How to Change a 1000 Separator to a 100 Separator in Excel – 3 Methods

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 are with 100 separators.

Read More: How to Change Decimal Separator in Excel

Method 2 – Using the Format Cells Dialog Box

You need to add 100 separators in 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.

Read More: How to Change Decimal Places in Excel

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)&"##")`

• 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]##\,##.

Related Articles

<< Go Back to Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Shajratul Alam Towhid

Md Shajratul Alam Towhid, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, holds a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep passion for research and innovation, he actively engages with Excel. In his capacity, Towhid not only adeptly tackles complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his unwavering commitment to consistently delivering exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF