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.
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.
Steps:
- Firstly, Copy and paste the data from 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 box.
- Fifthly, click OK.
- 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.
- 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.
- Eventually, the Format Cells dialogue box will appear.
- Fourthly, go to Number >
- Now, write the following text in the Type
[>=10000]##\,##\,##,00;[>=100]##\,##,00;#,00This 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 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)&"##")
- 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 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.
- 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 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.
- 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]##\,##.
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
- How to Add Decimals in Excel
- How to Reduce Decimals in Excel
- How to Reduce Decimal Places Permanently in Excel
- How to Remove Decimals in Excel
- How to Convert Decimal to Whole Numbers in Excel
- How to Convert Decimal to Fraction in Excel
- Excel Decimal Places Problem
- How to Remove Decimals in Excel Formula Bar
- How to Limit Decimal Places in Excel
<< Go Back to Decimals in Excel | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
