How to Remove Text before a Space with Excel Formula (5 Methods)

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.

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.

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

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

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

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

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

Related Articles

• How to Remove Text After Character in Excel (3 Ways)
• Remove Text from an Excel Cell but Leave Numbers (8 Ways)
• How to Remove Text between Two Characters in Excel (3 Easy Ways)