Remove Text Before Or After a Specific Character In ExcelLearn how to delete text strings before or after a specific character in Excel

  |

One of the most crucial things to do when working with data imported to Excel is to clean it.

By that, I mean deleting unnecessary characters such as extra spaces or stray special characters here and there.

Sometimes, you may have to delete a text string before or after a specific character such as a comma (,), pound (#), @, etc.

Here’s a visual representation of what I mean:

The data in the first table includes names and individual ages separated by a comma.

In the second table, only the names remain. Any text after the comma (and the comma itself) has been removed.

This is what I meant when deleting a text string before or after a specific character (a comma in this case).

Now while you can do the deleting manually, it’s only really feasible when you’re working with a small dataset.

Even then, there are more efficient ways to go with it.

I mean, I can just imagine the time that one has to consume to manually delete text strings on a cell-by-cell basis.

Fortunately, you’re reading this article. You won’t be manually deleting any text string before or after a specific character.

Instead, you’ll be using the techniques that you’ll from here.

You’ll be using the functions and features of Excel to remove text strings before or after a specific character.

Let’s get started.

Use the Find and Replace Feature to Remove Text Before or After a Specific Character

For the first method, you’ll be using Excel’s Find and Replace feature.

This nifty tool can be accessed in two ways: (1) press the keyboard shortcut Ctrl + H, or (2) press the Find & Select button from the Home tab.

Here’s what it looks like when you open Find and Replace:

What this feature does is it finds any instance of what you specified in the textbox after “Find what”, and then it replaces it with the value you entered in the textbox after “Replace with”.

You’ll be using this functionality to replace any text before or after a specific character with a blank, essentially deleting it.

To do so, you’ll be needing the help of a wildcard: the asterisk (*).

When entered in the textbox after “Find what”, it will serve as a placeholder for any number of characters.

So when you type “,*” in the textbox, it will find any text after a comma. If you type “*,” instead, Find and Replace will find any text before a comma.

How to Remove Text Before or After a Specific Character

For illustration, we’ll be using the following dataset:

Suppose you only want to retain the names. This means that you want to remove any text after the comma (including the comma itself). To do so, you’ll be using the Find and Replace feature.

  • Select the cells where you want to remove texts before or after a specific character.
  • Open the Find and Replace window. To do so, you may (1) press the keyboard shortcut Ctrl + H, or (2) press the Find & Select button from the Home tab.

  • In the textbox after “Find what”, enter the specific character then an asterisk (*) after it if you want to remove the text after it. If you want to remove the text before the specific character, type an asterisk(*) before it. In our illustration, we’ll be typing “,*” since we want to remove any text after a comma.

  • Make sure the textbox after “Replace with” is blank.

  • Click the Replace All button. Excel will inform you how many replacements it made.

  • Close the Find and Replace window. You have successfully removed the text before or after the specific character.

Things to Note

  • This method always removes the text before or after the first instance of the specific character. For example, it will remove any text before or after the first comma. If you only want to remove the text before or after the 2nd comma, this method won’t work.
  • If you only want to remove the first character of the text before or after the specific character, you can use the wildcard “?” instead of an asterisk
  • You can also use this method to remove any text string in between the 1st and 2nd instances of the specific character. For example, I want to remove any text between the 1st and 2nd To do so, I’ll enter “,*,” in the textbox after “Find what”.

Use Formula to Remove Text Before or After the First or Last Instance of a Specific Character

If you want more control over what you’ll be removing, you will have to use a formula instead of Find and Replace.

Here are the formulas you’ll be needing:

To Remove Text Before the First Instance of a Specific Character

The formula for removing text before the first instance of a specific character is as follows:

=RIGHT(cell,LEN(cell)-FIND(“character”,cell))

Where

cell – refers to the cell or text string from which you want to remove text

character – refers to the specific character which will be the basis for removing text; make sure to encase it with quotation marks

For example, I want to remove any text before the first comma of this cell:

The formula I’ll be using will be: =RIGHT(A2,LEN(A2)-FIND(“,”,A2)).

To Remove Text Before the Last Instance of a Specific Character

The formula for removing text before the last instance of a specific character is as follows:

=RIGHT(cell,LEN(cell)-FIND(“@”,SUBSTITUTE(cell,”character“,”@”,(LEN(cell)-LEN(SUBSTITUTE(cell,”character“,””)))/LEN(“character“))))

Where

cell – refers to the cell or text string from which you want to remove text

character – refers to the specific character which will be the basis for removing text; make sure to encase it with quotation marks

“@” can be any value. It only serves as a placeholder, which is why you won’t be seeing it in the result.

For example, I want to remove any text before the last comma of this cell:

The formula I’ll be using will be:

=RIGHT(A2,LEN(A2)-FIND(“@”,SUBSTITUTE(A2,”,”,”@”,(LEN(A2)-LEN(SUBSTITUTE(A2,”,”,””)))/LEN(“,”))))

To Remove Text After the First Instance of a Specific Character

The formula for removing text after the first instance of a specific character is as follows:

=LEFT(cell,FIND(“character“,cell)-1)

Where

cell – refers to the cell or text string from which you want to remove text

character – refers to the specific character which will be the basis for removing text; make sure to encase it with quotation marks

For example, I want to remove any text after the first comma of this cell:

The formula I’ll be using will be: =LEFT(A2,FIND(“,”,A2)-1)

To Remove Text After the Last Instance of a Specific Character

The formula for removing text after the last instance of a specific character is as follows:

=LEFT(cell,FIND(“@”,SUBSTITUTE(cell,”character“,”@”,LEN(cell)-LEN(SUBSTITUTE(cell,”character“,””))))-1)

Where

cell – refers to the cell or text string from which you want to remove text

character – refers to the specific character which will be the basis for removing text; make sure to encase it with quotation marks

“@” can be any value. It only serves as a placeholder, which is why you won’t be seeing it in the result.

For example, I want to remove any text after the last comma of this cell:

The formula I’ll be using will be:

=LEFT(A2,FIND(“@”,SUBSTITUTE(A2,”,”,”@”,LEN(A2)-LEN(SUBSTITUTE(A2,”,”,””))))-1)

Things to Note

  • The result of this method will appear on a different cell since you’ll be using formulas
  • If you want to remove text from cells within the same column, you can just use the fill handle to copy + paste the formula to the rest of the column (until the next empty row)

Use Formula to Remove Text Before or After the Nth Instance of a Specific Character

If you want to remove the text before or after any instance of a specific character (other than the first or last), then the above methods will not work.

You need to use another formula that will specify the instance of the specific character.

To Remove Text Before the Nth Instance of a Specific Character

The formula for removing text before the nth instance of a specific character is as follows:

=RIGHT(SUBSTITUTE(cell, “character“,”@”,instance), LEN(cell)- FIND(“@”,SUBSTITUTE(cell, “character “,”@”,instance),1)-1)

Where

cell – refers to the cell or text string from which you want to remove text

character – refers to the specific character which will be the basis for removing text; make sure to encase it with quotation marks

instance – refers to the nth instance of the specific character. For example, if you want the 2nd instance to be the reference, the instance will be 2

“@” can be any value. It only serves as a placeholder, which is why you won’t be seeing it in the result.

For example, I want to remove any text before the 2nd comma of this cell:

The formula I’ll be using will be:

=RIGHT(SUBSTITUTE(A2,”,”,”@”,2),LEN(A2)-FIND(“@”,SUBSTITUTE(A2,”,”,”@”,2),1)-1)

To Remove Text After the Nth Instance of a Specific Character

The formula for removing text after the nth instance of a specific character is as follows:

=LEFT(SUBSTITUTE(cell, “character“,”@”,instance),FIND(“@”,SUBSTITUTE(cell, “character “,”@”,instance),1)-1)

Where

cell – refers to the cell or text string from which you want to remove text

character – refers to the specific character which will be the basis for removing text; make sure to encase it with quotation marks

instance – refers to the nth instance of the specific character. For example, if you want the 2nd instance to be the reference, the instance will be 2

“@” can be any value. It only serves as a placeholder, which is why you won’t be seeing it in the result.

For example, I want to remove any text before the 2nd comma of this cell:

The formula I’ll be using will be:

=LEFT(SUBSTITUTE(A2,”,”,”@”,2),FIND(“@”,SUBSTITUTE(A2,”,”,”@”,2),1)-1)

Things to Note

  • The result of this method will appear on a different cell since you’ll be using formulas
  • If you want to remove text from cells within the same column, you can just use the fill handle to copy + paste the formula to the rest of the column (until the next empty row)

Conclusion

And those are the different ways you can remove any text before or after a specific character.

I hope you’ll be able to use your learnings here in your future endeavors.