While working with numbers in Excel, you may need to insert space(s) between numbers to have a clear look. Sometimes, the numbers of a cell are too long. For that, it becomes difficult to read. Spaces between numbers give us a clear look at that cell or range of cells. In this tutorial, we are going to show you how to add space between numbers in Excel with suitable examples and proper illustrations.

## Download Practice Workbook

Download this practice workbook.

## 3 Effective Approaches to Add Space between Numbers in Excel

To add space between numbers, we are going to demonstrate three effective methods that you can implement into your dataset. We recommend you learn and apply all these methods to your dataset. I hope it will solve your problem.

To demonstrate this tutorial, we are using the following dataset:

Here, we have a dataset consisting of some numbers. Now, our goal is to add space between the digits or a couple of digits of those numbers.

### 1. Use of Formulas to Add Space Between Numbers

Now, we will show four Excel formulas to insert space between numbers. All these formulas are composed of some Excel functions. Now, if you donâ€™t have many ideas about these functions, click the links to those functions. Surely, it will come in handy in the future.

#### 1.1 Use of the REPLACE Function

In Microsoft Excel, we use **the REPLACE function** to replace a specific portion of a text. The replacement performs based on the starting number, number of characters, and the text you want to add. We are going to use this function to denote the position of that space and insert that.

**The Generic Formula:**

**=REPLACE(old_text,start_num,num_chars,â€ť â€ś)**

*Now, from the dataset, we are going to add space in the middle of the numbers.*

đź“Ś** Steps**

1. First, type the following formula in **Cell C5**:

`=REPLACE(B5,4,0," ")`

2. Then, press **Enter**.

3. After that, drag the **Fill Handle** icon over the range of cells **C6:C10**.

As you can see, we have successfully added space between those numbers.

**Read More: How to Find and Replace Space in Excel (5 Methods)**

#### 1.2 Use of the CONCATENATE, LEFT, and RIGHT Functions

Now, this formula is a combination of three Excel functions. If we want to add any space at a particular place, we need to calculate the position from both the right and left sides. **The LEFT function** will count the position from the left and extract numbers. **The RIGHT function** will count from the right and will extract it. In the middle of those numbers, we will add a space. And finally, **the CONCATENATE function** will merge them all.

**The Generic Formula:**

**=CONCATENATE(LEFT(text,[num_chars])&â€ť â€ś&RIGHT(text,[num_chars]))**

*Now, from the dataset, we are going to add space in the middle of the numbers.*

đź“Ś **Steps**

1. First, type the following formula in **Cell C5**:

`=CONCATENATE(LEFT(B5,3)&" "&RIGHT(B5,3))`

2. Then, press **Enter**.

3. After that, drag the **Fill Handle** icon over the range of cells **C6:C10.**

In the end, you can see there are spaces in the middle of these numbers.

đź”Ž** How Does the Formula Work?**

*We are doing this breakdown for Cell B5*

**âž¤**** LEFT(B5,3)**

This function returns:** 133**

**âž¤**** RIGHT(B5,3)**

This function returns: **546**

**âž¤ CONCATENATE(LEFT(B5,3)&" "&RIGHT(B5,3))**

Finally, the **CONCATENATE **function will return: **133 546**

**Read More: How to Count Space Before Text in Excel (4 Ways)**

#### 1.3 Use of the MID Function along with CONCATENATE, LEFT, and RIGHT

If you want to separate the numbers into three parts or more, you can add** the MID function** in the previous formula. How to do that? Follow the steps below!

đź“Ś **Steps**

1. First, type the following formula in **Cell C5**:

`=CONCATENATE(LEFT(B5,2)&" "&MID(B5,3,2)&" "&RIGHT(B5,2))`

2. Then, press **Enter**.

3. After that, drag the **Fill Handle** icon over the range of cells **C6:C10**.

đź”Ž** How Does the Formula Work?**

*We are doing this breakdown for Cell B5*

**âž¤**** LEFT(B5,2)**

This function returns :** 13**

**âž¤**** MID(B5,3,2)**

This function returns :Â **35**

**âž¤**** RIGHT(B5,2)**

This function returns : **46**

**âž¤ CONCATENATE(LEFT(B5,2)&" "&MID(B5,3,2)&" "&RIGHT(B5,2))**

Finally, the **CONCATENATE **function will return: **13 35 46**

#### 1.4 Use of the TEXT Function

Now, we are using **the TEXT function** to change the format. You can add spaces between numbers. It will change the general format of that number.

We have two ways of doing that. The first one is the format â€ś**## ## ##**â€ť and the other one is â€ś**00 00 00**â€ś. You can choose any of that and implement this to your dataset. It will return you a similar result.

**The Generic Formula:**

**=TEXT(value,â€ť## ## ##â€ť)**

**Or,**

**=TEXT(value,â€ť00 00 00â€ł)**

*We are going to separate the numbers into three parts using spaces.*

đź“Ś **Steps**

1. First, type the following formula in **Cell C5**:

`=TEXT(B5,"## ## ##")`

**Or,**

`=TEXT(B5,"00 00 00")`

**Note:** As the numbers in our dataset have 6 digits each, and we want to insert a space after every 2 digits, we have used â€ś## ## ##â€ť or, â€ś00 00 00â€ť. The format will depend on the number of digits, space positions, etc.

2. Then, press **Enter**.

3. After that, drag the **Fill Handle** icon over the range of cells **C6:C10**.

As you can see, we have inserted spaces and separated the numbers into three parts.

**Read More: How to Add Space Between Text in Excel Cell (4 Easy Ways)**

#### 1.5 Use of the TEXT, LEN, and REPT Functions

Here, this formula is a combination of three functions. Earlier, we discussed the **TEXT** function. **The LEN function** gives us the length of any cell values. And **the REPT function** is generally used to repeat a text or a character a defined number of times in a cell.

**The Generic Formula:**

**=TEXT(value,REPT(â€ś0 â€ś,LEN(text)))**

*We are going to add spaces after every single number using this formula.*

đź“Ś **Steps**

1. First, type the following formula in **Cell C5**:

`=TEXT(B5,REPT("0 ",LEN(B5)))`

2. Then, press **Enter**.

3. After that, drag the **Fill Handle** icon over the range of cells **C6:C10**.

As you can see, we have added spaces after every single number.

đź”Ž **How Does the Formula Work?**

*We are doing this breakdown for Cell B5*

âž¤ **LEN(B5)**

This function returns: **6**

**âž¤ REPT("0 ",LEN(B5))**

This function returns : **0 0 0 0 0 0Â **

**âž¤ TEXT(B5,REPT("0 ",LEN(B5)))**

Finally, our number will be: **1 3 3 5 4 6Â **

**Similar Readings**

**How to Space Down in Excel (3 Methods)****Space Columns Evenly in Excel (5 Methods)****How to Space Rows Evenly in Excel (5 Methods)**

### 2. Using Number Formatting to Add Space Between Numbers

Now, this method is pretty simple to use. This method works like the TEXT function we discussed earlier. We will give our numbers a format. We can use spaces here between numbers.

đź“Ś **Steps**

1. First, select the range of cells** B5:B10**.

2. Then, go to **Home** Tab. You can find a dropdown menu there. Click on that.

3. Select **More Number Formats**.

4. Then, click on **Custom**.

5. In the **Type **box, type 00 00 00. You can also see the sample above.

6. Click on **OK**.

Here, you can see spaces between numbers. They are divided into three parts.

### 3. Using VBA Codes to Append Space Between Numbers in Excel

Now, if you know about Excelâ€™s **VBA** codes, this method will surely work for you. We are providing you with two **VBA** codes.

#### 3.1 Add Space after Each Digit

*This method will add a space after each digit as we did with formulas.*

đź“Ś **Steps**

1. First, press **ALT+F11** on your keyboard to open the VBA editor.

2. Click on **Insert > Module**.

3. Then, type the following code:

```
Sub InsertSpace()
Dim r As Range
Application.ScreenUpdating=False
For Each r In Range("B5",Range("B"&Rows.Count).End(xlUp))
r=Trim(Replace(StrConv(r,vbUnicode),Chr(0)," "))
Next r
Columns("B").AutoFit
Application.ScreenUpdating=True
End Sub
```

4. Now, select the range of cells** B5:B10**.

5. Then, press **ALT+F8**. It will open the **Macro **dialog box.

6. Click on **Run**.

After that, you will see added spaces after each digit.

#### 3.2. Add Space According to Userâ€™s Choice

Now, this formula is much useful and effective. You can add space in any position according to your choice. In the user-defined function, all you have to do is choose the cell and the position of the space.

đź“Ś **Steps**

1. First, press **ALT+F11** on your keyboard to open the VBA editor.

2. Click on **Insert > Module**.

3. Then, type the following code:

```
Function Space(cell As Range,position As Integer)
Space=Left(cell.Value,position)& " "&Right(cell.Value,Len(cell.Value)-position)
End Function
```

4. First, type the following formula in **Cell C5**:

`=Space(B5,2)`

5. Then, press **Enter**. You will see the space after the second position.

Finally, you can do the same for all numbers with different positions.

## đź’¬ Things to Remember

âśŽÂ In the first **VBA** code, you can add only **one** space.

âśŽÂ In the **TEXT** function, your **format** should be the same number of characters as the numbers. Otherwise, it will add a zero before them.

## Conclusion

To conclude, I hope this tutorial will help you to add space between numbers in Excel. We recommend you learn and apply all these methods to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this. Donâ€™t forget to check our website **ExcelDemy.com** for various Excel-related problems and solutions.