How to Change 1000 Separator to 100 Separator in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


What Is a Thousand Separator Format?

Thousand separator is a symbol (either comma or dot or space etc.) that 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 #,###.


How to Change 1000 Separator to 100 Separator in Excel: 3 Ways

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 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 by 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 from 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 box.
  • 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 separators.

Read More: How to Change Decimal Separator in Excel


2. Using Format Cells Dialog Box

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

Steps:

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

Using Format Cells Dialog Box

  • Secondly, select the cells we want to change and right-click on them.
  • 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 separators.

Read More: How to Change Decimal Places in Excel


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 a 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 the 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 the data from 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 separator.


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

Download Practice Workbook


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.


Related Articles


<< Go Back to Decimals in Excel | Number Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shajratul Alam Towhid
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo