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.

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

**Method 2 – Add Trailing Blank Spaces in Excel Using VBA**

**Step 1:**

- Select
**Visual Basic**from the**Develope**r 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 I**D**. 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

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

**Read More: **How to Space Columns Evenly in Excel

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

## Related Articles

- How to Space Rows Evenly in Excel
- How to Insert Tab in Excel Cell
- How to Add Space between Rows in Excel

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