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.
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.
- Firstly, Copy and paste the data of Column C in Column D.
- Secondly, go to File.
- 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.
- 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.
- Firstly, copy and paste the data of C Column in the D Column.
- 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.
- Eventually, the Format Cells dialogue box will appear.
- Fourthly, go to Number >
- Now, write the following text in the Type
This text is applicable for numbers greater than 9999 and less than 1000000.
- Fifthly, click OK.
- 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.
- 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
- 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.
- Firstly, copy and paste data of Column C in Column D.
- Secondly, select the cells D5:D13.
- Thirdly, 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 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.
- Firstly, copy and paste Column C values into Column D.
- Again, go to Format Cells by right-clicking on the selected cells.
- 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]##\,##.
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.