# How to Add Space between Numbers in Excel – 3 Easy Methods

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

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.

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

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.

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

Steps

• Enter the following formula in D5:
`=CONCATENATE(LEFT(C5,2)&" "&MID(C5,3,2)&" "&RIGHT(C5,2))`
• Press Enter.

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

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.

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

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.

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

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

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.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF