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

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

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

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:

 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

Add the MID function to the previous formula.

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

Use the TEXT function to change the number format.

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

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:

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

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:

• 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