How to Change 1000 Separator to 100 Separator in Excel

If you are looking for how to change 1000 separator to 100 separator in Excel, then you are in the right place. In Excel, while using data or large numbers we often need to separate the digits with a separator. In this article, we’ll try to discuss how to change 1000 separator to 100 separator in Excel.


Download Practice Workbook


What Is a Thousand Separator Format?

Thousand separator is a symbol (either comma or dot or space etc.) which we mainly use at the 3rd digit of any number. In the USA the character is comma (,), in Germany it is period (.). In different countries this character can be different. If # is a digit then the thousand separator format can be #,###.


3 Ways to Change 1000 Separator to 100 Separator in Excel

Excel offers different ways to change 1000 separators to 100 separators. We just need to follow some simple steps in each of the methods. Eventually, we have made a dataset named Total Export in 2021 which has column headers as Product Name and Price with 1000 Separators.

how to change 1000 separator to 100 separator in excel

We’ll try to discuss 3 methods to show this.


1. Using File Tab to Change 1000 Separator to 100 Separator in Excel

The easiest way to change 1000 separators to 100 separators is using the File tab. Suppose, in the following picture we have data in the Column C of Price with 1000 Separator. We need to change these data in Column D of Price with 100 Separator.

Using File Tab

Steps:

  • Firstly, Copy and paste the data of Column C in Column D.

Using File Tab

  • Secondly, go to File.

how to change 1000 separator to 100 separator in excel

  • Thirdly, go to Options.

  • Eventually, an Excel Options window will appear.
  • Fourthly, choose Advanced > deselect Use system separators > add comma (,) in the Decimal separator box and dot (.) in the Thousands separator
  • Fifthly, click OK.

how to change 1000 separator to 100 separator in excel

  • Consequently, we’ll see that the data of both C and D Columns are with 100 separator.


2. Using Format Cells Dialog Box

Using Format Cells dialogue box is another way to change 1000 separators to 100 separators.
We can add different types of text format in the Format Cells dialogue box according to the need of our output.
Here, we need to add 100 separator in the D Column.

Steps:

  • Firstly, copy and paste the data of C Column in the D Column.

Using Format Cells Dialog Box

  • Secondly, select the cells we want to change and right-click on it.
  • Thirdly, go to Format Cells.

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

how to change 1000 separator to 100 separator in excel

  • Eventually, the Format Cells dialogue box will appear.
  • Fourthly, go to Number >
  • Now, write the following text in the Type
[>=10000]##\,##\,##,00;[>=100]##\,##,00;#,00

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

  • Fifthly, click OK.

how to change 1000 separator to 100 separator in excel

  • Consequently, we’ll have the data in Column D with 100


3. Utilizing TEXT and REPT Functions

We can use the combination of TEXT and REPT functions to change 1000 separators to 100 separators. Suppose, we have Price with 1000 Separator in Column C of the following dataset. We want to get Price with 100 Separator in Column D.

  • Firstly, write the following formula in the D5 cell like this.
=TEXT(C5;REPT("##\,";MATCH(LEN(ABS(C5));{3;4;6})-1)&"##")

Utilizing TEXT and REPT Functions

  • Secondly, press ENTER to get the output.
  • Thirdly, use the Fill Handle by dragging down the cursor while holding the right-bottom corner of the D5

how to change 1000 separator to 100 separator in excel

  • Eventually, we’ll get all the outputs with 100 separators in D Column.


How to Add 1000 Separator in Excel

In Excel, we can also add 1000 separators. This method is also very easy to use.
We have Price without 1000 Separator in the Column C. We’ll try to get Price with 1000 Separator.
Steps:

  • Firstly, copy and paste data of Column C in Column D.

How to Add 1000 Separator in Excel

  • Secondly, select the cells D5:D13.
  • Thirdly, right-click the selected cells > go to Format Cells.

how to change 1000 separator to 100 separator in excel

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

  • Immediately, we’ll find all the outputs with 1000


How to Change Comma Separator in Excel

We can also add comma separators in Excel while using numbers. We have the following dataset where we want to add commas in Column D.

Steps:

  • Firstly, copy and paste Column C values into Column D.

How to Change Comma Separator in Excel

  • Again, go to Format Cells by right-clicking on the selected cells.

how to change 1000 separator to 100 separator in excel

  • Thirdly, in the Number option, go to Number > choose any of the options in the Negative Numbers box > click OK.

  • Finally, we have added comma separators like this in the 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 desired text is a comma (,).
  • With this text, if we type any number, it becomes complicated. Because, if we type 99 Excel will show it as ,99 and in the case of 10,00,00 Excel will display 1000,00.
  • To solve this under 100 format we need to modify the text by adding a [>=100] and the whole text becomes [>=100]##\,##.

Conclusion

That’s all about today’s session. And these are the ways to change 1000 separators to 100 separators in Excel. We strongly believe this article would be highly beneficial for you. Don’t forget to share your thoughts and queries in the comments section and explore our website Exceldemy, a one-stop Excel solution provider.

Towhid

Towhid

Hello, myself Shajratul Alam Towhid. Basically, I am a Naval Architect who wants to expand knowledge in the field of Microsoft Excel. I wish all of my articles will be beneficial for the readers.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo