How to delete text before or after a certain character in Excel

How to remove everything after a specific character

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

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:

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)))

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:

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:

The key part of the formula is the FIND function that calculates the position of the nth delimiter (comma in our case). Here's how:

We replace the 2^nd comma in A2 with a hash symbol (or any other character that does not exist in your data) with the help of SUBSTITUTE:

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

The resulting string goes to the 2^nd argument of FIND, so it finds the position of "#" in that string:

FIND("#", "Emma, Design# (102) 123-4568")

FIND tells us that "#" is the 13^th character in the string. To know the number of characters preceding it, just subtract 1, and you'll get 12 as the result:

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

This number goes directly to the num_chars argument of LEFT asking it to pull the first 12 characters from A2:

=LEFT(A2, 12)

That's it!

How to delete text before Nth occurrence of a character

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

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:

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.

To achieve the goal, you can remove text after the last comma in A2 with this formula:

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

Copy the formula down the column, and you'll get this result:

How this formula works:

The gist of the formula is that we determine the position of the last delimiter (comma) in the string and pull a substring from left up to the delimiter. Getting the delimiter's position is the trickiest part, and here's how we handle it:

First, we find out how many commas there are in the original string. For this, we replace each comma with nothing ("") and serve the resulting string to the LEN function:

LEN(SUBSTITUTE(A2, ",",""))

For A2, the result is 35, which is the number of characters in A2 without commas.

Subtract the above number from the total string length (38 characters):

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

… and you will get 3, which is the total number of commas in A2 (and also the ordinal number of the last comma).

Next, you use the already familiar combination of the FIND and SUBSTITUTE functions to get the position of the last comma in the string. The instance number (3^rd comma in our case) is supplied by the above-mentioned LEN SUBSTITUTE formula:

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

It appears that the 3^rd comma is the 23^rd character in A2, meaning we need to extract 22 characters preceding it. So, we put the above formula minus 1 in the num_chars argument of LEFT:

LEFT(A2, 23-1)

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:

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):

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

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 :)

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:

1. 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.
2. Depending on whether uppercase and lowercase letters should be treated as different or the same characters, check or uncheck the Case-sensitive box.
3. 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:

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

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