How To Remove Text Before Or After a Specific Character In Excel

When working with text data in Excel, you may have the need to remove the text before or after a specific character or text string.

For example, if you have the name and designation data of people, you may want to remove the designation after the comma and only keep the name (or vice-versa where you keep the designation and remove the name).

Sometimes, it can be done with a simple formula or a quick Find and Replace, and sometimes, it needs more complex formulas or workarounds.

In this tutorial, I will show you how to remove the text before or after a specific character in Excel (using different examples).

So let’s get started with some simple examples.

Remove Text After a Character Using Find and Replace

If you want to quickly remove all the text after a specific text string (or before a text string), you can do that using Find and Replace and wild card characters.

Suppose you have a dataset as shown below and you want to remove the designation after the comma character and keep the text before the comma.

Below are the steps to do this:

1. Copy and Paste the data from column A to column  B (this is to keep the original data as well)
2. With the cells in Column B selected, click on the Home tab
3. In the Editing group, click on the Find & Select option
4. In the options that appear in the drop-down, click on the Replace option. This will open the Find and Replace dialog box
5. In the ‘Find what’ field, enter ,* (i.e., comma followed by an asterisk sign)
6. Leave the ‘Replace with’ field empty
7. Click on the Replace All button

The above steps would find the comma in the data set and remove all the text after the comma (including the comma).

Since this replaces the text from the selected cells, it’s a good idea to copy the text into another column and then perform this find and replace operation, or create a backup copy of your data so that you have the original data intact

How does this work?

* (asterisk sign) is a wildcard character that can represent any number of characters.

When I use it after the comma (in the ‘Find what’ field) and then click on Replace All button, it finds the first comma in the cell and considers it a match.

This is because the asterisk (*) sign is considered a match to the entire text string that follows the comma.

So when you hit the replace all button, it replaces the comma and all the text that follows.

In case you want to remove all the characters before the comma, change the find what field entry by having the asterisk sign before the comma (*, instead of ,*)

Remove Text Using Formulas

If you need more control over how to find and replace text before or after a specific character, it’s better to use the inbuilt text formulas in Excel.

Suppose you have the below data set Where you want to remove all the text after the comma.

Below is the formula to do this:

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

The above formula uses the FIND function to find the position of the comma in the cell.

This position number is then used by the LEFT function to extract all the characters before the comma. Since I don’t want comma as a part of the result, I have subtracted 1 from the Find formula’s resulting value.

This was a simple scenario.

Let’s take a slightly complex one.

Suppose I have this below data set where I want to remove all the text after the second comma.

Here is the formula that will do this:

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

Since this data set has multiple commas, I cannot use the FIND function to get the position of the first comma and extract everything to the left of it.

I need to somehow find out the position of the second comma and then extract everything which is to the left of the second comma.

To do this, I have used the SUBSTITUTE function to change the second comma to an exclamation mark. Now this gives me a unique character in the cell. I can now use the position of the exclamation mark to extract everything to the left of the second comma.

This position of the exclamation mark is used in the LEFT function to extract everything before the second comma.

So far so good!

But what if there is an inconsistent number of commas in the data set.

For example, in the below data set some cells have two commas and some cells have three commas, and I need to extract all the text before the last comma.

In this case, I need to somehow figure out the position of the last occurrence of the comma and then extract everything to the left of it.

Below is the formula that will do that

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

The above formula uses the LEN function to find the overall length of the text in the cell as well as the length of the text without the comma.

When I subtract these two values, it gives me the total number of commas in the cell.

So, it would give me 3 for cell A2 and 2 for cell A4.

This value is then used within the SUBSTITUTE formula to replace the last comma with an exclamation mark. And then you can use the left function extract everything which is to the left of the exclamation mark (which is where the last comma used to be)

As you can see in the examples, using a combination of text formulas allows you to handle many different situations.

Also, since the result is linked with the original data, when you change the original data the result would automatically update.

Remove Text Using Flash Fill

Flash Fill is a tool that was introduced in Excel 2013 and is available in all the versions after that.

It works by identifying patterns when you manually enter the data and then extrapolated to give you the data for the entire column.

So if you want to remove the text before or after a specific character, you just need to show flash fairy what the result would look like (by entering it manually a couple of times ), and flash fill would automatically understand the pattern and give you the results.

Let me show you this with an example.

Below I have the data set where I want to remove all the text after the comma.

Here are the steps to do this using Flash Fill:

1. In cell B2, which is the adjacent column of our data, manually enter ‘Jeffery Haggins’ (which is the expected result)
2. In cell B3, enter ‘Tim Scott’ (which is the expected result for the second cell)
3. Select the range B2:B10
4. Click the Home tab
5. In the Editing group, click on the Fill drop-down option
6. Click on Flash Fill

The above steps would give you the result as shown below:

You can also use the Flash Fill keyboard shortcut Control + E after selecting the cells in the result column (Column B in our example)

Flash Fill is a wonderful tool and in most cases, it is able to recognize the pattern and give you the right result. but in some cases, it may not be able to recognize the pattern correctly and can give you the wrong results.

So make sure you double-check your Flash Fill results

And just like we have removed all the text after a specific character using flash fill, you can use the same steps to remove the text before a specific character. just show flash fill how the result should look like my Internet manually in the adjacent column, and it would do the rest.

Remove Text Using VBA (Custom Function)

The whole concept of removing the text before or after a specific character Is dependent on finding the position of that character.

As if seen above, finding the last occurrence of that character good means using a concoction of formulas.

If this is something you need to do quite often, you can simplify this process by creating a custom function using VBA (called User Defined Functions).

Once created, you can re-use this function again and again. It’s also a lot simpler and easier to use (as most of the heavy lifting is done by the VBA code in the back end).

Below the VBA code that you can use to create the custom function in Excel:

Function LastPosition(rCell As Range, rChar As String)
'This function gives the last position of the specified character
'This code has been developed by Sumit Bansal (https://trumpexcel.com)
Dim rLen As Integer
rLen = Len(rCell)

For i = rLen To 1 Step -1

If Mid(rCell, i - 1, 1) = rChar Then
LastPosition = i - 1
Exit Function
End If

Next i

End Function

You need to place the VBA code in a regular module in the VB Editor, or in the Personal Macro Workbook. Once you have it there, you can use it as any other regular worksheet function in the workbook.

This function takes 2 arguments:

1. The cell reference for which you want to find the last occurrence of a specific character or text string
2. The character or text string whose position you need to find

Suppose you now have the below data set and you want to remove all the text after the last comma and only have the text before the last comma.

Below is the formula that will do that:

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

In the above formula, I have specified to find the position of the last comma. In case you want to find the position of some other character or text string, you should use that as the second argument in the function.

As you can see this is a lot shorter and easier to use, as compared to the long text formula that we used the previous section

If you put the VBA code in a module in a workbook you would only be able to use this custom function in that specific workbook. If you want to use this in all the workbooks on your system, you need to copy and paste this code in the Personal Macro Workbook.

So these are some of the mails you can use to quickly remove the text before or after a specific character in Excel.

If it’s a simple one-time thing, then you can do that using find and replace. what if it is a little more complex, then you need to use a combination of inbuilt Excel formulas, or even create your own custom formula using VBA.

I hope you found this tutorial useful.

Other Excel tutorials that you may like:

• Count Characters in a Cell (or Range of Cells) Using Formulas in Excel
• How to Remove the First Character from a String in Excel
• How to Compare Text in Excel
• How to Remove Dashes (-) in Excel?
• How to Remove Comma in Excel (from Text and Numbers)
• How to Extract the First Word from a Text String in Excel
• Separate First and Last Name in Excel (Split Names Using Formulas)
• Remove Characters From Left in Excel (Easy Formulas)
• Remove Last Character in Excel