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.

**Table of Contents**hide

## 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 **#,###**.

## 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 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**.

**Steps:**

- 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**.**

**Read More: How to Change Decimal Separator in Excel**

### 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.**

- 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**

`[>=10000]##\,##\,##,00;[>=100]##\,##,00;#,00`

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**

**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 **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**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**.

- 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**.

**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.