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.

**Table of Contents**hide

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

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

**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**Develope**r 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**.

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

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

- 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 I**D 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 I**D 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!!!