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.

Download Practice Workbook

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

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

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.

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.

Read More: How to Add Space Between Text in Excel Cell 

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 (2 Easy Approaches)

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.

• 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.

Similar Readings

• How to Add Space between Rows in Excel
• Find and Replace Space in Excel (5 Methods)
• How to Remove White Space in Excel (6 Easy Ways)

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.

• 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.

• 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.

Read More: How to Add Space between Numbers in Excel

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.

Read More: How to Space Columns Evenly in Excel (5 Methods)

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.

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!!!

Further Readings

• How to Space Down in Excel (3 Methods)
• Count Space Before Text in Excel (4 Ways)
• How to Space Rows Evenly in Excel (5 Methods)