How to Add Blank Space in Excel Formula: 6 Suitable Methods

The image below shows the worksheet that we are going to work with.

Method 1 – Add Blank Spaces Trailing Text Values in Excel

We’ll use the following dataset.

Step 1:

Select cell C5 and enter the following formula in that cell.

=LEFT(B5 & REPT(" ",10),10)

Formula Breakdown:

The REPT function will insert a space 10 times.
We then extract the first 10 characters from the result.
The First Name will have enough spaces to make the text justified and tidy.

Step 2:

Drag the fill handle of cell C5 down to apply the formula to the rest of the cells below.

You will see that First Names in Added Space column now have extra spaces, and their positions in the cells have shifted slightly to the left.

Step 3:

Copy the First Names with added spaces and the Last Names into the Notepad. The texts will be justified.

Method 2 – Add Trailing Blank Spaces in Excel Using VBA

Step 1:

Select Visual Basic from the Developer tab.

Click on the Insert button and select Module.

Step 2:

Insert the following code in the window that appears.

Sub Add_Blank_Space()
For i = 5 To 14 
    Range("D" & i) = Range("B" & i) & Space(1) & Range("C" & i)
Next            
 End Sub

Step 3:

Click Run.

If a window named Macro appears, click Run from that window.

You will now see that the Full Name column is filled with the full names of all employees.

Read More: How to Space out Cells in Excel

Method 3 – Add Blank Spaces Between Two Text Values Using the CONCATENATE Function in Excel

Step 1:

Enter the following formula in cell D5.

=CONCATENATE(B5, " ", C5)

Formula Breakdown:

The CONCATENATE formula takes 3 arguments. The first is the First Name (B5). The second is the space denoted by a pair of quotes (“ ”) with a space between them. The last one is the Last Name (C5).

After clicking Enter, you will get the full name in cell D5.

Step 2:

Drag the fill handle of cell D5 down to apply the formula to the rest of the cells below.

You will see that the Full Name column is filled with the full names.

Method 4 – Add Blank Spaces Between Uniform Cell Values to Separate in Different Parts

Step 1:

Enter the following formula in cell D5.

=LEFT(D5,3)& " "&RIGHT(D5,5)

Formula Breakdown:

Each ID No has 3 letters. The LEFT function will extract these 3 characters as we have instructed it to extract only the first 3 characters (Second Argument) from the left of the ID No or Cell D5 (First Argument).
The RIGHT function will extract the 5 characters from the right or end of the ID No or cell D5 (First Argument).
The &” ”& will add a space between the extracted letters and characters.

After clicking ENTER, you will get the Separated ID No in cell E5.

Step 2:

Drag the fill handle of cell E5 down to apply the formula to the rest of the cells below.

The Separated ID No column is filled with the separated ID Nos of all full names.

Method 5 – Add a Blank Space Using Excel Formula Before the First Number in a Cell Value

Enter the formula below in cell E5.

=TRIM(REPLACE(D5,MIN(FIND({1,2,3,4,5,6,7,8,9,0},D5&"1234567890")),0," "))

After clicking ENTER, we will get the Separated ID No in cell E5. The values are separated before the first number.

Step 2:

AutoFill the formula to the rest of the cells below.

You will now see that the Separated ID No column is filled with the separated ID. Each value is separated at the position of the first number in that value.

Method 6 – Add a Blank Space Between Cell Values and Borders in Excel

Step 1:

Select all the cells in the Full Name.
Right-click on a selected cell.
Select Format Cells.

Step 2:

A new window titled Format Cells will appear. Click on the Alignment tab from that window.
Enter a value in the Indent input box.
Click the OK button to confirm the new indent value.

An extra indent or space has been added to each cell in the Full Name.

Things to Remember

If you do not have a Developer tab, you can make it visible in File > Options > Customize Ribbon.
To open the VBA editor press ALT + F11.
You can press ALT + F8 to bring up the Macro window.

Download the Practice Workbook

Add Blank Space.xlsm

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

Get FREE Advanced Excel Exercises with Solutions!