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.

How to Add Blank Space Using Excel Formula


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.

Add Blank Spaces Trailing Text Values in Excel 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.

LEFT and REPT Functions in Excel

Step 2:

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

drag the fill handle of cell downward to apply the formula to the rest of cells

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

How to Add Blank Space Using Excel Formula

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.

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.

select Visual Basic from the Developer

  • Now, click on the Insert button and select Module.

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.

click on the Run button

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

click on Run from the Macro window

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

How to Add Blank Space Using Excel Formula

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.
=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).

Add Blank Spaces Between Two Text Values Using CONCATENATE

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

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.

drag the fill handle of cell downward to apply the formula to the rest of cells

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

Full Name column is filled with the full names of all employees


Similar Readings


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.

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

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

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.

drag the fill handle of cell downward to apply the formula to the rest of cells

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

How to Add Blank Space in Excel Formula


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," "))

Add Blank Space Before the First Number in a Cell Value

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

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.

drag the fill handle of cell downward to apply the formula to the rest of cells

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

How to Add Blank Space Using Excel Formula

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.

Add Space Between Cell Values and Borders in Excel

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.

Insert Indent Value

  • We will now see that an extra indent or space has been added to each cell in the Full Name.

extra indent or space has been added to each cell

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

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo