The dataset below showcases numbers with and without spacing.

The following dataset contains population data of 6 cities in the United States.

### Method 1 Using Formulas to Add a Space Between Numbers

#### 1.1 Use the REPLACE Function to Add a Space Between Numbers

Use **the REPLACE function** to replace a specific portion of a text.

**The Generic Formula is:**

**=REPLACE(old_text,start_num,num_chars,” “)**

**Steps**

- Enter the following formula in
**D5**:

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

- Press
**Enter**. A space was added between the 3rd and 4th digits in**C5**.

- Drag down the
**Fill Handle**to autofill**D6:D10**.

#### 1.2 Using the CONCATENATE, LEFT, and RIGHT Functions to Add a Space Between Numbers

**The LEFT function** will count the position from the left and extract numbers. **The RIGHT function** will count from the right and will extract numbers. In the middle of those numbers, we will add a space. **The CONCATENATE function** will merge functions.

**Generic Formula:**

**=CONCATENATE(LEFT(text,[num_chars])&” “&RIGHT(text,[num_chars]))**

**Steps**

- Enter the following formula in
**D5**:

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

- Press
**Enter**. A space was added between the 3rd and 4th digits in**C5**.

- Drag down the
**Fill Handle**to autofill**D6:D10**.

**Formula Breakdown**

**LEFT(C5,3)**

returns:** 133**

**RIGHT(C5,3)**

returns: **546**

**➤ CONCATENATE(LEFT(C5,3)&” “&RIGHT(C5,3))**

returns **133 546**

#### 1.3 Using the MID Function with the CONCATENATE, LEFT, and RIGHT Functions to Add a Space Between Numbers

Add** the MID function** to the previous formula.

**Steps**

- Enter the following formula in
**D5**:

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

- Press
**Enter**.

- Drag down the
**Fill Handle**to autofill**D6:D10**.

**Formula Breakdown**

**LEFT(C5,2)**

returns :** 13**

**MID(C5,3,2)**

returns : **35**

**RIGHT(C5,2)**

returns : **46**

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

returns:

**13 35 46**

#### 1.4 Using the TEXT Function to Add a Space Between Numbers

Use **the TEXT function** to change the number format.

**Generic Formula:**

**=TEXT(value,”## ## ##”)**

**Or,**

**=TEXT(value,”00 00 00″)**

Separate the numbers into three parts using spaces.

**Steps**

- Enter the following formula in
**D5**:

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

**Or,**

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

**Note:** As the numbers in the dataset have 6 digits, to insert a space after every 2 digits, use “## ## ##” or, “00 00 00”.

- Press
**Enter**.

- Drag down the
**Fill Handle**to autofill**D6:D10**.

**Read More:**How to Add Space Between Text in Excel Cell

#### 1.5 Using the TEXT, LEN, and REPT Functions to Add a Space Between Numbers

**The LEN function** gives us the length of cell values. **The REPT function** is used to repeat a text or a character a defined number of times in a cell.

**Generic Formula:**

**=TEXT(value,REPT(“0 “,LEN(text)))**

Add spaces after every single number.

**Steps**

- Enter the following formula in
**D5**:

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

- Press
**Enter**.

- Drag down the
**Fill Handle**to autofill**D6:D10**.

**Formula Breakdown**

**LEN(B5)**

returns: **6**

**REPT(“0 “,LEN(B5))**

returns : **0 0 0 0 0 0 **

**TEXT(B5,REPT(“0 “,LEN(B5)))**

returns: **1 3 3 5 4 6 **

### Method 2 – Using the Number Formatting to Add a Space Between Numbers

**Steps**

- Select
**C5:C10**.

- Go to the
**Home**Tab. - Select
**More Number Formats**.

- Click
**Custom**.

- In
**Type**, enter 00 00 00.

- Click
**OK**.

There are spaces between the numbers..

### Method 3 – Using VBA Codes to Add a Space Between Numbers in Excel

Use Excel’s **VBA** codes.

#### 3.1 Adding a Space after Each Digit

**Steps**

- Press
**ALT+F11**to open the VBA editor. - Click
**Insert >> Module**.

- Enter the following code:

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

- Select
**C5:C10**.

- Press
**ALT+F8**to open the**Macro**dialog box.

- Click
**Run**.

- A Space was added after each digit.

#### 3.2. Customizing Spaces between Numbers

#### Choose the cell and the position of the space.

**Steps**

- Press
**ALT+F11**to open the VBA editor. - Click
**Insert >> Module**. - Enter 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
```

A custom function (**Space**) with 2 arguments was created. The 1st argument is the selected cell. The 2nd argument is the number of digits after which the space will be added.

- Save the module by pressing
**CTRL+S**. - Go back to the main worksheet.
- Enter the following formula in
**D5**:

`=Space(C5,2)`

- Press
**Enter**. You will see a space after the two first digits.

- You can use the formula for other numbers and choose different positions.

## Things to Remember

- In the first
**VBA**code, you can add only**one**space. It will work in column**C**only. - In the
**TEXT**function, your**format**must have the same number of characters as the number. Otherwise, it will add a zero before the number.

**Download Practice Workbook**

Download this exercise workbook to practice.

## Related Articles

- How to Add Blank Space Using Excel Formula
- Add Space between Rows in Excel
- How to Space out Cells in Excel
- How to Insert Tab in Excel Cell
- Space Columns Evenly in Excel
- How to Space Rows Evenly in Excel

**<< Go Back to Space in Excel | Text Formatting | Learn Excel**