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:

Steps

• Enter the following formula in D5:

• 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

Generic Formula:

 Steps

• Enter the following formula in D5:

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

• Drag down the Fill Handle to autofill D6:D10.

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:

• Press Enter.

• Drag down the Fill Handle to autofill D6:D10.

1.4 Using the TEXT Function to Add a Space Between Numbers

Generic Formula:

Or,

Separate the numbers into three parts using spaces.

Steps

• Enter the following formula in D5:

Or,

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

Generic Formula:

Add spaces after every single number.

Steps

• Enter the following formula in D5:

• Press Enter.

• Drag down the Fill Handle to autofill D6:D10.

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:

• 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