Often we need to remove the text before a space using an excel formula. Although there are several ways to delete text before a space, using formulas is more convenient. Luckily we can write these formulas using excel’s regular functions or we can use **VBA User Defined Functions**. This article will guide you to apply formulas while removing texts before space.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.

## 5 Ways to Use Excel Formula to Remove Text before a Space

### 1. Erase Text before a Space Using Excel Formula with RIGHT, LEN & SEARCH Functions

Let’s consider a dataset containing several people’s full names. Here first and last names are separated by a space. Now I will remove the text before this space and keep only the last names. While doing this deletion process I will use the combination of **RIGHT**, **LEN**, and **SEARCH** functions. Follow the below steps to get the ultimate result.

**Steps:**

- First, type the below formula in
**Cell D5**and press**Enter**.

`=RIGHT(B5,LEN(B5)-SEARCH(" ",B5))`

- As a result, we will get the below result. The text ‘
**Andrew**’ – before the space is removed as we wanted. Now, use the**Fill Handle**(**+**) tool to copy the formula over the range**C6:C10**.

- Finally, text before all the last names and spaces are deleted as shown in the below screenshot.

🔎 **How Does the Formula Work?**

**SEARCH(” “,B5)**

Here, the** SEARCH** function returns the location of the space (“ ”) in the string of **Cell B5**, which is:

{**7**}

**LEN(B5)**

Then, the **LEN** function returns the total number of characters present in **Cell B5**:

{**11**}

**LEN(B5)-SEARCH(” “,B5)**

Later, this part of the formula subtracts the space position from the total length of **Cell B5** and results:

{**4**}

**RIGHT(B5,LEN(B5)-SEARCH(” “,B5))**

Finally, the **RIGHT** function extracts** 4** characters from the right side of **Cell B5** and returns:

{**Yang**}

**Read More:** **How to Remove Text From Excel Cell (9 Easy Ways)**

### 2. Combine RIGHT, LEN & FIND Functions to Remove Text before First Space in Excel

You can use the text before a space by combining the **RIGHT**, **LEN**, and **FIND **functions. Using this combination, you can remove the text before 1st space if there is more than one space present in a string. Suppose, I have a dataset containing several people’s biographical data (**Name**, **Profession**, **Age**) separated by spaces. Now, I will remove the text before the first space from each of these data.

**Steps:**

- Type the below formula in
**Cell D5**and hit**Enter**from the keyboard.

`=RIGHT(B5,LEN(B5)-FIND(" ",B5))`

- Consequently, the text before the first space is removed and upon applying the
**Fill Handle**tool here is the ultimate result.

- The above formula works in a similar way described in
**Method1**, except, here the**FIND**function returns the location of the first space from the entered string.

**Read More:** **How to Remove Specific Text from a Column in Excel (8 Ways)**

### 3. Delete Text before Last Space with Excel Formula

Unlike the previous method, now I will delete text before the last space in a string. While doing that I will apply a formula based on **RIGHT**, **LEN**, **SEARCH**, and **SUBSTITUTE** functions in excel.

**Steps:**

- Type the following formula in
**Cell D5**. Then press**Enter**.

`=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5," ","#",LEN(B5)-LEN(SUBSTITUTE(B5," ","")))))`

- Upon entering the formula here is the output we receive. We can see that all the texts before the last space in these strings are not anymore.

🔎 **How Does the Formula Work?**

**SUBSTITUTE(B5,” “,””)**

Here, the **SUBSTITUTE** function substitutes all space of** Cell B5** into nothing and returns:

{“**AndrewTeacher33**“}

**LEN(SUBSTITUTE(B5,” “,””))**

Then the **LEN **function returns the length of the result of the **SUBSTITUTE **formula mentioned above and returns:

{**15**}

**LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))**

This part returns:

{**2**}

**SUBSTITUTE(B5,” “,”#”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””)))**

Now, this part of the formula replies:

{**“Andrew Teacher#33”**}

**SEARCH(“#”,SUBSTITUTE(B5,” “,”#”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))))**

Subsequently, the above formula returns:

{**15**}

**RIGHT(B5,LEN(B5)-SEARCH(“#”,SUBSTITUTE(B5,” “,”#”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””)))))**

In conclusion, the formula deletes the texts before **2nd **space and replies:

{**33**}

**Read More:** **How to Delete Defined Names in Excel (3 Ways)**

### 4. Apply Formula to Remove Text before N-th Space in Excel

This time, I will delete text before the **N**-th occurrence of the space in a string. Suppose I have the below dataset. From these data, now, I will remove all the text before the **2nd **space.

**Steps:**

- Type the below formula in
**Cell D5**. Next, hit**Enter**.

`=RIGHT(SUBSTITUTE(B5," ","#",2),LEN(B5)-FIND("#",SUBSTITUTE(B5," ","#",2)))`

- After entering the formula, we will get the below result.

🔎 **How Does the Formula Work?**

**SUBSTITUTE(B5,” “,”#”,2)**

Here the **SUBSTITUTE** function replaces the **2nd** space of the string of **Cell B5** with a ‘**#**’.

{**“Andrew Teacher#33 M”**}

**FIND(“#”,SUBSTITUTE(B5,” “,”#”,2))**

Then, the **FIND **function returns the location of the 2nd space which is:

{**15**}

**RIGHT(SUBSTITUTE(B5,” “,”#”,2),LEN(B5)-FIND(“#”,SUBSTITUTE(B5,” “,”#”,2)))**

Finally, the **RIGHT **function, in combination with** LEN** and **SUBSTITUTE **function removes the text before 2nd space of the string of **Cell B5** and returns:

{**33 M**}

**Read More:** **How to Remove Specific Text from Cell in Excel (Easiest 11 Ways)**

### 5. Excel VBA UDF to Delete Text before Space

In this method, I will create a **User Defined Function** (**UDF**) using excel** VBA**. Later, using that **UDF** I will remove text from a string.

**Syntax of UDF:**

For a better understanding of the formula let’s get introduced to the **UDF **I will create:

**DeleteText(string, character, occurrence, is_before)**

Where

**string**is the original string.**character**indicates the ‘space’ here.**occurrence**is the position of ‘space’ in the original string.**is before**is the boolean value which indicates from which side of the space we want to delete the text.

Follow the below instructions to create and apply the abovementioned **UDF**.

**Steps:**

- Firstly, go to
**Developer**>**Visual Basic**to Bring the**VBA**window. You can bring the**VBA**window by using**Alt + F11**too.

- As the
**VBA**window appears, right-click on the**VBA Project**and go to**Insert**>**Module**.

- Secondly, type the below code in the newly inserted
**Module**.

```
Function DeleteText(str As String, Character As String, occurrence As Integer, is_before As Boolean)
Dim Space_no, start_num, Space_len As Integer
Dim str_output As String
Space_no = 0
start_num = 1
str_output = ""
Space_len = Len(Character)
For i = 1 To occurrence
Space_no = InStr(start_num, str, Character, vbTextCompare)
If 0 < Space_no Then
start_num = Space_no + Space_len
End If
Next i
If 0 < Space_no Then
If False = is_before Then
str_output = Mid(str, 1, start_num - Space_len - 1)
Else
str_output = Mid(str, start_num)
End If
End If
DeleteText = str_output
End Function
```

- Now, go to the excel sheet where you want to apply the function and start to type the newly created function. The function will appear as other excel functions (see screenshot).

- Then, enter arguments of the function to remove all the space before the
**2nd**space.

`=DeleteText(B5," ",2,TRUE)`

- After that press
**Enter**and apply the**Fill Handle**tool to copy the formula to the rest of the cells. - Finally, here we receive the expected result. Texts before the
**2nd**space from all the strings are removed.

**Related Content: ****How to Remove Letters from Cell in Excel (10 Methods)**

## Things to Remember

- Other than using excel formulas you can use the
**Flash Fill**option in excel to delete text before space.

## Conclusion

In the above article, I have tried to discuss several methods to use an excel formula to remove the text before a space elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.