How to Remove Text after a Specific Character in Excel?

by

When editing or cleaning data in your worksheet, one often needs to trim and remove certain parts of strings, either for formatting consistency or to make the dataset look neat and prim.

For example, you might need to remove text after the ‘@’ symbol of email addresses to get just the usernames.

You might need to remove items of a list after a specific symbol, like say the third comma symbol. There are many other instances where you might need to remove text after a specific character in Excel.

In this tutorial we will show you 3 ways to do that:

  • Using Find and Replace
  • Using a Formula
  • Using VBA

We will also show you how to remove text after the nth instance of a specific character (like after the second hyphen or the third comma).

Removing Text after a Specific Character

Let us assume we have the following dataset of email addresses and we want to remove everything after the ‘@’ character in each cell:

Email dataset

There are three ways to get this done. Let us see each method one by one.

Method 1: Using Find and Replace to Remove Text after a Specific Character

Excel’s Find and Replace dialog box is quite versatile as it provides a very efficient way to find items on your worksheet and get things done quickly.

All you need to do is enter your search string to specify what you want to replace and then specify what you want to replace it with.

One great feature of this dialog box is that it lets you use wildcards to help you find what you need. For example, you can use the question mark (?) wildcard to be a placeholder for a single character in your search string.

So if you type ‘b?t’ as the search string, it will find the words bat, bit, bot, etc. (if they exist on your worksheet).

In our example, we are going to use the asterisk (*) wildcard. This wildcard lets you give a placeholder for any number of characters in your search string.

So if you type ‘@*’ as the search string, the Find and Replace feature will find everything after and including the ‘@’ symbol in each cell.

Let us see step by step how you can use Find and Replace to remove text after the ‘@’ symbol:

  1. Select the range of cells you want to work with.
  2. Click on Find & Select from the Home tab (under the ‘Editing’ group)
  3. This will display a dropdown menu. Select ‘Replace’.
  4. This will open the Find and Replace dialog box. Type ‘@*’ in the text box next to ‘Find what’.
  5. Leave the text box next to ‘Replace with’ blank. This is because you want to delete everything after the ‘@’ symbol in each cell.
  6. Click on the ‘Replace All’ button.Find and replace dialogue box to remove everything after @

This will replace all the text after the ‘@’ symbol from all the cells.

Text after a Specific Character in Excel result

Also read: How to Remove the Last Digit in Excel?

Method 2: Using a Formula to Remove Text after a Specific Character

The second method uses a formula that combines together the LEFT and the FIND Excel functions.

The LEFT function is used to extract a given number of characters from a text, starting from the left-most character. It’s syntax is:

=LEFT(text, num_of_characters)

Here,

  • text is the string from which you want to extract characters.
  • num_of_characters is the number of characters you want to extract from the text, starting from the left.

The FIND function is used to find the index or location of a character or substring inside a text. It’s syntax is:

=FIND(search_string,text)

Here,

  • text is the text that you want to search for. This can be a string or cell references.
  • search_string is the character or substring for which you want to find the index in text.

To remove text after a character, you need to combine the above two functions into the following formula:

=LEFT(text,FIND(character,text)-1)

Let us see step by step how you can use this formula to remove text after the ‘@’ symbol in our example:

Email dataset

  1. Select the first cell of the column where you want the results to appear. In our example, it will be cell B2.
  2. Type the formula: =LEFT(A2,FIND(“@”,A2)-1).
  3. Press the return key.
  4. This will give you the text obtained after removing everything following the ‘@’ symbol from the string in cell A2.Fromula to remove all text after the @ symbol
  5. Double click the fill handle (located at the bottom-left corner) of cell B2. This will copy the formula to all the other cells of column B. You can also choose to drag down the fill handle to achieve the same effect. Here are both the original and converted columns side by side:Formula result with only the username
  6. If you want to retain only the converted versions of the text, then select these cells (B2:B11), copy them, and paste them in the same place as values.
  7. You can then delete column A if you need to.Delete the extra column

How did this Formula Work?

To understand how this formula worked, we need to break it down:

  1. First, we used the FIND function to find the position of the ‘@’ character in the text: FIND(“@”, A2).
  2. We want all characters before (and not including) the “@” symbol. So we subtracted 1 from this index: FIND(“@”, A2)-1. This gives the index of the last character before the ‘@’ symbol.
  3. Finally, we used the LEFT function to extract only the characters starting from the left up to that index: LEFT(A2,FIND(“@”,A2)-1)

Thus we get all the characters in A2 from leftmost character to the character before the ‘@’ symbol!

Also read: How to Remove Question Marks from Text in Excel?

Method 3: Using VBA to Remove Text after a Specific Character

Finally, if you’re comfortable with VBA and want to get your job done quickly through a macro, this is a great method for you.

Here’s the code we will be using. You can select and copy it:

Sub remove_text_after_char()
Dim rng As Range
Dim cell As Range
Set rng = Application.Selection
For Each cell In rng
cell.Offset(0, 1).Value = Left(cell, InStr(cell, "@") - 1)
Next cell
End Sub

Follow these steps:

  1. From the Developer Menu Ribbon, select Visual Basic.
  2. Once your VBA window opens, Click Insert->Module. Now you can start coding. Type or copy-paste the above lines of code into the module window. Your code is now ready to run.
  3. Select the range of cells containing the text you want to convert. Make sure the column next to it is blank because this is where the macro will display the results.
  4. Navigate to Developer->Macros->remove_text_after_char->Run.
  5. You will now see the truncated strings next to your selected range of cells.

VBA result after removing data after a specific character

Note: You can change the symbol in line 6 from “@” to your required symbol.

Do remember to keep a backup of your sheet, because the results of VBA code are usually irreversible.

Also read: How to Clear Formulas in Excel

Removing Text after the nth Instance of a Specific Character

Let us now see a special case where you need to remove all text after a particular instance of a character.

For example, in the dataset shown below, say you want to remove all text after the second comma. (Maybe you want to only keep the first two items):

Dataset with comma

For this, we can just tweak the formula we used in method 2, by adding the SUBSTITUTE function. The SUBSTITUTE function lets you substitute a given instance of a character in a text with another character. Its syntax is:

=SUBSTITUTE (original_string, old_character, new_character,instance_number)

Here,

  • original_string is the text or reference to the cell that you want to work on.
  • old_character is the character that you want to replace.
  • new_character is the character that you want to replace old_character with.
  • instance_number is the instance of the old_character that you want to replace. That means if you want to replace the second comma in original_string, this value will be 2.

So here are the steps to follow if you want to remove all text after the second comma in our dataset:

  1. Select the first cell of the column where you want the results to appear. In our example, it will be cell B2.
  2. Type the formula: 
    =LEFT(A2,FIND("#",SUBSTITUTE(A2,",","#",2))-1).
  3. Press the return key.
  4. This will give you the text obtained after removing everything following the second comma symbol from the string in cell A2.formula to remove text after nth occurence of a character
  5. Double click the fill handle (located at the bottom-left corner) of cell B2. This will copy the formula to all the other cells of column B. You can also choose to drag down the fill handle to achieve the same effect. Here are both the original and converted columns side by side:Result where text string has been removed after nth occurrence
  6. If you want to retain only the converted versions of the text, then select these cells (B2:B5), copy them, and paste them in the same place as values.
  7. You can then delete column A if you need to.Delete the column after done

How did this Formula Work?

To understand how this formula worked, we need to break it down:

  1. First, we used the SUBSTITUTE function to find the second instance of the comma symbol in the text: SUBSTITUTE(A2,”,”,”#”,2). Just to differentiate it from the other comma symbols in the cell, we substituted this comma with a hash symbol (#). We could have used any other symbol too, it doesn’t really matter. What matters is that we made sure it is a symbol that is different from all the other symbols in the text. This ensures that the FIND function (that we will be using next) can find this character.
  2. Next we use the FIND function to find the position of the ‘#’’ character in the text: FIND(“#”,SUBSTITUTE(A2,”,”,”#”,2)).
  3. We want all characters before (and not including) the “#” symbol. So we subtract 1 from this index: FIND(“#”,SUBSTITUTE(A2,”,”,”#”,2))-1. This gives the index of the last character before the “#” symbol (which was originally the second comma).
  4. Finally, we used the LEFT function to extract only the characters starting from the left up to that index: LEFT(A2,FIND(“#”,SUBSTITUTE(A2,”,”,”#”,2))-1).

Thus we get all the characters in A2 from leftmost character to the character before the second comma symbol!

If you want to extract a substring before any other occurrence of the comma symbol, you can use the same formula, but just replace the number “2” with the instance of the comma that you want to stop at.

So if you want to remove everything after the third comma, your formula would change to:

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

If you want to extract a substring before the nth occurrence of any other character you just need to replace the comma symbol to your required character.

In this tutorial, we showed you three ways to remove text after a specific character. We also showed you how to remove text after any instance of a character in a given text.

We tried to present the methods in simple and easy steps. We do hope you found our instructions easy to understand and apply.

Other Excel tutorials you may like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment