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

Solve the issue in the image below using multiple Excel formulas together.

Step 1:

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

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

Formula Breakdown:

The LEFT function takes 2 arguments. Taking the First Name (B5) and the REPT function as the first argument.
The REPT function in Excel also takes 2 arguments. Argument one, a character or string that we want the function to repeat. Inserted two quote marks (“ “) separated by a space. To add space into the text repeatedly. Argument two, a numeric value that tells the REPT function how many times it should repeat the character or string. In this case, it is 10. The function will repeat or add space after the value of cell B5 10 times.
The LEFT function is a numeric value. This numeric value will tell the LEFT how many characters it should extract from the left side of the first or text. Example, 10 for the second argument of the LEFT, the LEFT function will extract 10 characters from the left of cell B5 that has 10 trailing spaces after it.
The First Name will have enough spaces to make the text justified and tidy.

Step 2:

Drag the fill handle of cell C5 downward 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 after as their position 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. Also press ALT+F11 to open it.

Click on the Insert button and select Module.

Step 2:

Write down 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 CONCATENATE Function in Excel

Step 1:

Enter the below formula in cell D5.

=CONCATENATE(B5, " ", C5)

Formula Breakdown:

The CONCATENATE formula takes 3 arguments. First, the First Name (B5). Second the space denoted by a pair of quotes (“ ”) with a space between them. And 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 downward 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 below 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 letters 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 numbers trailing the 3 letters at the beginning as it is extracting the 5 characters (Second Argument) 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 by the 2 functions.

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

Step 2:

Drag the fill handle of cell E5 downward 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 Blank Space Using Excel Formula Before the First Number in a Cell Value

If the values in ID No are a random combination of letters and numbers, use multiple functions of Excel to separate the values into 2 parts inserting spaces before the first numbers of the values.

First, 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 at the position of the first number.

Step 2:

Fill handle of cell E5 downward to apply 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 respective first number in that value.

Method 6 – Add 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. A window will appear. We will select Format Cells from there.

Step 2:

A new window titled Format Cells will appear. We will 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 > Option > Customize Ribbon.
To open the VBA editor Press ALT + F11.
You can press ALT + F8 to bring up the Macro window.

Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.

Add Blank Space.xlsm

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

Get FREE Advanced Excel Exercises with Solutions!