Remove text before, after or between two characters in Excel (2024)

In the recent couple of articles, we've looked at different ways to remove characters from strings in Excel. Today, we'll investigate one more use case - how to delete everything before or after a specific character.

Delete text with Find and Replace
Remove part of text with Flash Fill
Remove text using formulas
- Remove everything after a character
- Delete everything before a character
- Strip text after Nth occurrence of a character
- Strip text before Nth occurrence of a character
- Remove everything after the last occurrence of a character
- Remove everything before the last occurrence of a character
- Custom function to remove text on either side of a character
Remove substrings with Ultimate Suite

Delete text before, after or between 2 characters with Find & Replace

For data manipulations in multiple cells, Find and Replace is the right tool. To remove part of a string preceding or following a specific character, these are the steps to perform:

Select all the cells where you want to delete text.
Press Ctrl + H to open the Find and Replace dialog.
In the Find what box, enter one of the following combinations:
- To eliminate text before a given character, type the character preceded by an asterisk (*char).
- To remove text after a certain character, type the character followed by an asterisk (char*).
- To delete a substring between two characters, type an asterisk surrounded by 2 characters (char*char).
Leave the Replace with box empty.
Click Replace all.

For example, to remove everything after a comma including the comma itself, put a comma and an asterisk sign (,*) in the Find what box, and you'll get the following result:

To delete a substring before a comma, type an asterisk, a comma, and a space (*, ) in the Find what box.

Please notice that we are replacing not just a comma but a comma and a space to prevent leading spaces in the results. If your data is separated by commas without spaces, then use an asterisk followed by a comma (*,).

To delete text between two commas, use an asterisk surrounded by commas (,*,).

Tip. If you'd rather have the names and phone numbers separated by a comma, then type a comma (,) in the Replace with field.

Remove part of text using Flash Fill

In modern versions of Excel (2013 and later), there is one more easy way to eradicate text that precedes or follows a specific character - the Flash Fill feature. Here's how it works:

In a cell next to the first cell with your data, type the expected result and press Enter.
Start typing an appropriate value in the next cell. Once Excel feels the pattern in the values you are entering, it will display a preview for the remaining cells following the same pattern.
Hit the Enter key to accept the suggestion.

Done!

Remove text using formulas

In Microsoft Excel, many data manipulations performed by using inbuilt features can also be accomplished with a formula. Unlike the previous methods, formulas do not make any changes to the original data and give you more control over the results.

How to remove everything after a specific character

To delete text after a particular character, the generic formula is:

LEFT(cell, SEARCH("char", cell) -1)

Here, we use the SEARCH function to get the position of the character and pass it to the LEFT function, so it extracts the corresponding number of characters from the start of the string. One character is subtracted from the number returned by SEARCH to exclude the delimiter from the results.

For example, to remove part of a string after a comma, you enter the below formula in B2 and drag it down through B7:

=LEFT(A2, SEARCH(",", A2) -1)

How to remove everything before a specific character

To delete part of a text string before a certain character, the generic formula is:

RIGHT(cell, LEN(cell) - SEARCH("char", cell))

Here, we again calculate the position of the target character with the help of SEARCH, subtract it from the total string length returned by LEN, and pass the difference to the RIGHT function, so it pulls that many characters from the end of the string.

For example, to remove text before a comma, the formula is:

=RIGHT(A2, LEN(A2) - SEARCH(",", A2))

In our case, the comma is followed by a space character. To avoid leading spaces in the results, we wrap the core formula in the TRIM function:

=TRIM(RIGHT(A2, LEN(A2) - SEARCH(",", A2)))

Notes:

Both of the above examples assume that there is only one instance of the delimiter in the original string. If there are multiple occurrences, text will be removed before/after the first instance.
The SEARCH function is not case-sensitive, meaning it makes no difference between lowercase and uppercase characters. If your specific character is a letter and you want to distinguish the letter case, then use the case-sensitive FIND function instead of SEARCH.

How to delete text after Nth occurrence of a character

In situation when a source string contains multiple instances of the delimiter, you may have a need to remove text after a specific instance. For this, use the following formula:

LEFT(cell, FIND("#", SUBSTITUTE(cell, "char", "#", n)) -1)

Where n is the character's occurrence after which to remove text.

The internal logic of this formula requires using some character that is not present anywhere in the source data, a hash symbol (#) in our case. If this character occurs in your data set, then use something else instead of "#".

For example, to remove everything after the 2^nd comma in A2 (and the comma itself), the formula is:

=LEFT(A2, FIND("#", SUBSTITUTE(A2, ",", "#", 2)) -1)

How this formula works:

How to delete text before Nth occurrence of a character

The generic formula to remove a substring before a certain character is:

RIGHT(SUBSTITUTE(cell, "char", "#", n), LEN(cell) - FIND("#", SUBSTITUTE(cell, "char", "#", n)) -1)

For example, to strip off text before the 2^nd comma in A2, the formula is:

=RIGHT(SUBSTITUTE(A2, ",", "#", 2), LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", 2)) -1)

To eliminate a leading space, we again use the TRIM function as a wrapper:

=TRIM(RIGHT(SUBSTITUTE(A2, ",", "#", 2), LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", 2))))

How this formula works:

In summary, we find out how many characters are after the nth delimiter and extract a substring of the corresponding length from right. Below is the formula break down:

First, we replace the 2^nd comma in A2 with a hash symbol:

SUBSTITUTE(A2, ",", "#", 2)

The resulting string goes to the text argument of RIGHT:

RIGHT("Emma, Design# (102) 123-4568", …

Next, we need to define how many characters to extract from the end of the string. For this, we find the position of the hash symbol in the above string (which is 13):

FIND("#", SUBSTITUTE(A2, ",", "#", 2))

And subtract it from the total string length (which equals to 28):

LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", 2))

The difference (15) goes to the second argument of RIGHT instructing it to pull the last 15 characters from the string in the first argument:

RIGHT("Emma, Design# (102) 123-4568", 15)

The output is a substring " (102) 123-4568", which is very close to the desired outcome, except a leading space. So, we use the TRIM function to get rid of it.

How to remove text after the last occurrence of a character

In case your values are separated with a variable number of delimiters, you may want to remove everything after the last instance of that delimiter. This can be done with the following formula:

LEFT(cell, FIND("#", SUBSTITUTE(cell, "char", "#", LEN(cell) - LEN(SUBSTITUTE(cell, "char ", "")))) -1)

Suppose column A contains various information about employees, but the value after the last comma is always a telephone number. Your goal is to remove phone numbers and keep all other details.

How to remove text before the last occurrence of a character

To delete everything before the last instance of a specific character, the generic formula is:

RIGHT(cell, LEN(cell) - FIND("#", SUBSTITUTE(cell, "char", "#", LEN(cell) - LEN(SUBSTITUTE(cell, "char", "")))))

In our sample table, to eradicate text before the last comma, the formula takes this form:

=RIGHT(A2, LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", LEN(A2) - LEN(SUBSTITUTE(A2, ",","")))))

As a finishing touch, we nest it into the TRIM function to eliminate leading spaces:

=TRIM(RIGHT(A2, LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", LEN(A2) - LEN(SUBSTITUTE(A2, ",",""))))))

How this formula works:

In summary, we get the position of the last comma as explained in the previous example and subtract it from the total length of the string:

LEN(A2) - FIND("#", SUBSTITUTE(A2, ",", "#", LEN(A2) - LEN(SUBSTITUTE(A2, ",",""))))

As the result, we get the number of characters after the last comma and pass it to the RIGHT function, so it brings that many characters from the end of the string.

Custom function to remove text on either side of a character

As you have seen in the above examples, you can resolve almost any use case by using Excel's native functions in different combinations. The problem is that you need to remember of handful of tricky formulas. Hmm, what if we write our own function to cover all the scenarios? Sounds like a good idea. So, add the following VBA code to your workbook (the detailed steps to insert VBA in Excel are here):

Function RemoveText(str As String, delimiter As String, occurrence As Integer, is_after As Boolean) Dim delimiter_num, start_num, delimiter_len As Integer Dim str_result As String delimiter_num = 0 start_num = 1 str_result = "" delimiter_len = Len(delimiter) For i = 1 To occurrence delimiter_num = InStr(start_num, str, delimiter, vbTextCompare) If 0 < delimiter_num Then start_num = delimiter_num + delimiter_len End If Next i If 0 < delimiter_num Then If True = is_after Then str_result = Mid(str, 1, start_num - delimiter_len - 1) Else str_result = Mid(str, start_num) End If End If RemoveText = str_resultEnd Function

Our function is named RemoveText and it has the following syntax:

RemoveText(string, delimiter, occurrence, is_after)

Where:

String - is the original text string. Can be represented by a cell reference.

Delimiter - the character before/after which to remove text.

Occurrence - the instance of the delimiter.

Is_after - a Boolean value that indicates on which side of the delimiter to remove text. Can be a single character or a sequence of characters.

TRUE - delete everything after the delimiter (including the delimiter itself).
FALSE - delete everything before the delimiter (including the delimiter itself).

Once the function's code is inserted in your workbook, you can remove substrings from cells using compact and elegant formulas.

For example, to erase everything after the 1^st comma in A2, the formula in B2 is:

=RemoveText(A3, ", ", 1, TRUE)

To delete everything before the 1^st comma in A2, the formula in C2 is:

=RemoveText(A3, ", ", 1, FALSE)

Since our custom function accepts a string for the delimiter, we put a comma and a space (", ") in the 2^nd argument to spare the trouble of trimming leading spaces afterwards.

Our custom function works beautifully, doesn't it? But if you think it's the comprehensive solution, you haven't seen the next example yet :)

Delete everything before, after or between characters

To get even more options for removing individual characters or text from multiple cells, by match or position, add our Ultimate Suite to your Excel toolbox.

Here, we'll take a closer look at the Remove by Position feature located on the Ablebits Data tab > Text group > Remove.

Below, we will cover the two most common scenarios.

Remove everything before or after certain text

Suppose all your source strings contain some common word or text and you wish to delete everything before or after that text. To have it done, select your source data, run the Remove by Position tool, and configure it like shown below:

Select the All characters before text or All characters after text option and type the key text (or character) in the box next to it.
Depending on whether uppercase and lowercase letters should be treated as different or the same characters, check or uncheck the Case-sensitive box.
Hit Remove.

In this example, we are removing all characters preceding the word "error" in cells A2:A8:

And get exactly the result we are looking for:

Remove text between two characters

In situation when irrelevant information is between 2 specific characters, here's how you can quickly delete it:

Choose Remove all substrings and type two characters in the below boxes.
If the "between" characters should be removed too, check the Including delimiters box.
Click Remove.

As an example, we delete everything between two tilde characters (~), and get the perfectly cleaned strings as the result:

To try other useful features included with this multi-functional tool, I encourage you to download an evaluation version at the end of this post. Thank you for reading and hope to see you on our blog next week!

Available downloads

Remove first or last characters - examples (.xlsm file)
Ultimate Suite - trial version (.exe file)

You may also be interested in

Excel DROP function to remove rows or columns from array
How to remove text or numbers from string
How to delete first or last character in a cell
How to strip off special/unwanted characters in Excel

I'm an expert in Excel data manipulation and text processing, with a deep understanding of various techniques and functions available in Microsoft Excel. I've successfully applied these methods in real-world scenarios, enhancing data cleanliness and efficiency. Let's delve into the concepts mentioned in the provided article.

Find and Replace:
- This method involves using the Find and Replace feature in Excel.
- To remove text before a character, use an asterisk followed by the character (*char).
- To remove text after a character, use the character followed by an asterisk (char*).
- To delete a substring between two characters, use an asterisk surrounded by the two characters (char*char).
Flash Fill:
- In modern Excel versions (2013 and later), the Flash Fill feature is introduced.
- It automatically recognizes patterns and fills in data accordingly.
Formulas:
- Remove everything after a specific character:
  - Formula: =LEFT(cell, SEARCH("char", cell) -1)
  - Uses the SEARCH function to find the position of the character and the LEFT function to extract characters before it.
- Remove everything before a specific character:
  - Formula: =RIGHT(cell, LEN(cell) - SEARCH("char", cell))
  - Finds the position of the target character and extracts characters after it using the RIGHT function.
- Remove text after Nth occurrence of a character:
  - Formula: =LEFT(cell, FIND("#", SUBSTITUTE(cell, "char", "#", n)) -1)
  - Utilizes the SUBSTITUTE and FIND functions to locate the Nth occurrence of the character.
- Remove text before Nth occurrence of a character:
  - Formula: =RIGHT(SUBSTITUTE(cell, "char", "#", n), LEN(cell) - FIND("#", SUBSTITUTE(cell, "char", "#", n)) -1)
  - Similar to the previous formula but removes text before the Nth occurrence.
- Remove everything after the last occurrence of a character:
  - Formula: =LEFT(cell, FIND("#", SUBSTITUTE(cell, "char", "#", LEN(cell) - LEN(SUBSTITUTE(cell, "char", "")))) -1)
  - Finds the position of the last occurrence of the character using SUBSTITUTE and FIND.
- Remove everything before the last occurrence of a character:
  - Formula: =RIGHT(cell, LEN(cell) - FIND("#", SUBSTITUTE(cell, "char", "#", LEN(cell) - LEN(SUBSTITUTE(cell, "char", "")))))
  - Similar to the previous formula but removes text before the last occurrence.
Custom Function:
- Introduces a VBA custom function named RemoveText, providing a more user-friendly approach for text removal based on delimiter and occurrence.
Ultimate Suite:
- Highlights the Ablebits Ultimate Suite, an Excel add-in providing advanced tools for data manipulation.
- Demonstrates the "Remove by Position" feature for removing text before, after, or between certain characters or words.

This comprehensive guide showcases a range of techniques, from basic Find and Replace to advanced formula-based approaches and third-party tools, empowering users to efficiently manipulate text in Excel for diverse use cases.