How to Add Blank Space Using Excel Formula (6 Methods)

Sometimes while working with Excel you might need to add blank space before and after the cell values. You might need to add the blank spaces to separate a cell value into different parts or sometimes you would need to join two cell values together to make a unified text like joining the first and last names to get the full name of a person. In this article, I will show you how to add blank space using the Excel formula.

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

Let’s assume a scenario where we have an Excel file that contains information about the employees of a company. The worksheet has the First Name, Last Name, Full Name, and their ID No. We will use this worksheet to add blank space after the First Name using an Excel formula so the First and Last Names of all the employees will be justified when we copy them to Notepad or any text editor. We will also add space between First Name and Last Name to get the Full Name of an employee. Along with that, we will add spaces to separate the text and numeric values in ID No. The image below shows the worksheet that we are going to work with.

1. Add Blank Spaces Trailing Text Values in Excel

Sometimes when we try to copy adjacent cell values from Excel into Notepad or any text editor, chances are the copied values will not be justified and will be untidy. The image below shows the First Names and Last Names of the employees become untidy when we copy them from Excel to Notepad.

We can solve this problem using multiple Excel formulas together. Just follow the below steps.

Step 1:

First, we will select cell C5 and enter the below formula in that cell.

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

Formula Breakdown:

The LEFT function takes 2 arguments. The first one is a text. In this example, it is taking the First Name (B5) and the REPT function as the first argument.
The REPT function in Excel also takes 2 arguments. The first one is a character or string that we want the function to repeat. We have inserted two quote marks (“ “) separated by a space. This means we want the function to add space into our text repeatedly. The second argument is a numeric value that tells the REPT function how many times it should repeat the character or string. In this case, it is 10. So, the function will repeat or add space after the value of cell B5 10 times.
The second argument of 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. We have inserted 10 for the second argument of the LEFT So, the LEFT function will extract 10 characters from the left of cell B5 that has 10 trailing spaces after it.
So, the First Name will now have enough spaces after it to make the text justified and tidy.

Step 2:

We will now drag the fill handle of cell C5 downward to apply the formula to the rest of the cells below.

If we look carefully, we will see that First Names in Added Space column now have extra spaces after them as their positions in the cells have shifted slightly to the left.

Step 3:

If we now copy the First Names with added spaces and the Last Names into the Notepad, we will see that the texts have been justified and look tidy.

2. Add Trailing Blank Spaces in Excel Using VBA

Another very efficient way to add trailing blank spaces after cell values in Excel is to use the VBA. For example, we will add the First Names and Last Names together with a space between them to create the Full Names of the employees.

Step 1:

First, we will select Visual Basic from the Developer tab. We can also press ALT+F11 to open it.

Now, 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:

Now, click on the Run.

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

We 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

3. Add Blank Spaces Between Two Text Values Using CONCATENATE Function in Excel

Excel has a function named CONCATENATE. We can use it to add or join the text values with spaces between them.

Step 1:

First, enter the below formula in cell D5.

=CONCATENATE(B5, " ", C5)

Formula Breakdown:

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

Upon clicking the ENTER button, we will get the full name in cell D5.

Step 2:

We will now drag the fill handle of cell D5 downward to apply the formula to the rest of the cells below.

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

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

We can use the LEFT and RIGHT functions in Excel to separate the uniform cell values in a range by adding blank spaces. For example, we will separate the values in the ID No column into 2 parts by adding space between the text part at the start of the ID No of each employee and the rest of the numerical part. If you look carefully, you will notice that each ID No has 3 letters at the start and 5 numbers after these letters.

Step 1:

First, enter the below formula in cell D5.

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

Formula Breakdown:

This formula might look hard to understand but it is actually very easy to figure out what this formula is doing.
Each ID No has 3 letters at the beginning. 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.

Upon clicking the ENTER button, we will get the Separated ID No in cell E5.

Step 2:

We will now drag the fill handle of cell E5 downward to apply the formula to the rest of the cells below.

We will now see that the Separated ID No column is filled with the separated ID Nos of all employees.

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, we can use multiple functions of Excel to separate the values into 2 parts by inserting spaces before the first numbers of the values.

First, enter the below formula in cell E5.

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

Upon clicking the ENTER button, we will get the Separated ID No in cell E5. The values are separated at the position of the first number.

Step 2:

We will now drag the fill handle of cell E5 downward to apply the formula to the rest of the cells below.

We will now see that the Separated ID No column is filled with the separated ID Nos of all employees. Each value is separated at the position of the respective first number in that value.

6. Add Blank Space Between Cell Values and Borders in Excel

If you work with a large Excel worksheet with a lot of columns, then adding extra padding between the cell boundaries and values will let you work with such a large worksheet easily and comfortably. For example, we will add extra padding or spaces in the cells of the Full Name column. Follow the below steps to add extra padding in cells.

Step 1:

First, we will select all the cells in the Full Name. We will then 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.
We will then enter a value in the Indent input box.
Finally, we will click on the OK button to confirm the new indent value.

We will now see that 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

Conclusion

In this article, we have learned how to add blank space using the Excel formula in different ways. I hope from now on you can add blank space using the excel formula easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!

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

Get FREE Advanced Excel Exercises with Solutions!

How to Add Blank Space Using Excel Formula (6 Methods)

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

1. Add Blank Spaces Trailing Text Values in Excel

2. Add Trailing Blank Spaces in Excel Using VBA

3. Add Blank Spaces Between Two Text Values Using CONCATENATE Function in Excel

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

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

6. Add Blank Space Between Cell Values and Borders in Excel

Things to Remember

Conclusion

Related Articles

Leave a reply Cancel reply