How to Remove Text After Character in Excel (4 Easy Ways)

While working with a large dataset, sometimes you may need to remove some certain parts of the data. To do this manually is really time consuming and naive. In Excel, you can remove text after a certain character in 4 efficient ways.

Download Practice Workbook

You can download the workbook from here.

4 Easy Ways to Remove Text After Character in Excel

Suppose we have a dataset containing some data of the name and profession of some people and we want to remove the profession from the dataset or just have the first name.

Now, using this dataset we will show you how to remove text after a certain character by using Find & Replace command tool, different functions, Flash Fill feature and also by utilizing VBA code.

1. Use Find & Replace Command to Delete Text After Character

We will learn how to remove text after a certain character by using the Find & Replace feature in Excel. In Excel, this is the easiest way to delete any text after a specific character.

The steps to do that are given below.

Steps:

• Firstly, select your dataset.
• Then, under the Home tab, go to Find & Select >> click on Replace.

• Now, the Find and Replace box will open.
• After that, beside the Find what label, write the character after which you want to remove the text and put an Asterisk (*) symbol after it.
• Next, keep the Replace with box empty.
• Lastly, press Replace All.
• However, to understand more, check the following picture, where we want to remove everything after comma (,), so we write the symbol comma (,) and put an Asterisk (*) symbol along with it.

• Afterward, a pop-up will open to confirm your replacements.
• Then, click on OK.

• Thus, it will remove all the text after comma (,) in your dataset.

Read More: How to Remove Text After Character in Excel (3 Ways)

2. Apply Excel LEFT Function to Remove Text After Character

Unlike the above method, using the LEFT function in formulas to remove strings after a specific character in Excel is a more efficient and reliable method. Implementing formulas produce the more unchanged and controllable result of your dataset.

Using formulas opens several ways to delete the text after several specific points of the string.

2.1 Delete All Text After a Character

Firstly, we will use the LEFT and SEARCH functions to delete all text after a character in Excel.

Generic Formula: 

Here,

• cell = cell reference number of your data.
• character = the character after which you want to remove the text.

If you want to remove all text after a character, then just follow the given steps below.

Steps:

• In the beginning, in the cell beside your dataset, write the following formula.

• Then, press Enter and drag the row down using Fill Handle to apply the formula to the rest of the dataset.

• Thus, it will remove all the text after a certain character.

2.2 Remove Text After N-th Occurrence of a Character

In the situation, when you have to remove text after a certain character, but that character occurs multiple times in the string, then you need to use the following formula,

Generic Formula:

Here,

• cell = cell reference number of your data.
• character = the character after which you want to remove the text.
• n = the character’s occurrence after which to remove text.
• # = to introduce a new character that is not present anywhere in the source dataset (you can use any character that you want but keep in mind that, that character has to be unique).

The steps for removing text after the N-th occurrence of a character are given below.

Steps:

• To start with, In the cell beside your dataset, write the following formula and press Enter.

• Then, drag down the Fill Handle tool for the rest of the cells.
• Moreover, to understand it clearly, check the above example where we want to remove everything after the 2nd comma (,) in Cell B5.

• Finally, it will give you the result of the truncated dataset without all the text after comma (,).

2.3 Remove Text After Last Occurrence of a Character

In case of deleting all the text after a certain character that occurred multiple times, you need to implement the following formula,

Generic Formula:

Here,

• cell = cell reference number of your data.
• character = the character after which you want to remove the text.
• # = to introduce a new character that is not present anywhere in the source dataset (you can use any character that you want but keep in mind that, that character has to be unique).

The steps of removing text after the last occurrence of a character are given below.

Steps:

• Firstly, In the cell beside your dataset, write the following formula.

• After that, press Enter and drag the row down using Fill Handle to apply the formula to the rest of the dataset.

• Thus, it will remove all the text after a certain character.

Read More: How to Remove Text From Excel Cell (9 Easy Ways)

3. Utilize Flash Fill Feature to Remove Text in Excel

You can also utilize the Flash Fill feature to remove text after a character in Excel. To do that, you have to introduce Excel to some patterns it has to follow.

Here are the steps to do that.

Steps:

• In the beginning, manually type “Josh” (our expected result) in Cell C5 as it is the adjacent cell of our data.

• Similarly, type “Joe” in Cell C6.

• Then, select cell range C5:C9.
• After that, go to the Home tab >> expand Editing >> click on Fill >> select Flash Fill.
• However, you can also press Ctrl + E after selecting the cell range to use the Flash Fill feature.

• Thus, following the pattern all the cells will remove text after a character.

4. Remove Text After Character Using VBA in Excel

If you are an experienced Excel user, then this method is only for you. Using VBA to remove text after a character is the quickest way to get the job done.

Steps:

• Firstly, press Alt + F11 on your keyboard or go to the tab Developer >> click on Visual Basic to open Visual Basic Editor.

• Then, from the menu bar, click on Insert >> select Module.

• Next, copy the following code and paste it into the code window.

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

• After that, click on the Save button to save the code.

• Now, switch to the worksheet of interest, select the desired range, make sure to keep the column next to it blank because the macro will display the result here.
• Then, navigate to Developer >> click on Macros.

• Lastly, select the macro name remove_text_after_character >> click on Run.

• Finally, it will show you the result of removing text after a character in Excel.

Read More: How to Remove Specific Text from a Column in Excel (8 Ways)

Practice Section

In this section, we are giving you the dataset to practice on your own and learn to use these methods.

Conclusion

In this article, we have learned how to remove text after a certain character in Excel. We find out how to do that not only from the 1st occurrence of the character but also the last and Nth occurrences. I hope that this article has been very beneficial to you. Feel free to ask any questions if you have any questions regarding the topic. And visit ExcelDemy for many more articles like this. Thank you!

Related Articles

• How to Delete Defined Names in Excel (3 Ways)
• Remove Text from an Excel Cell but Leave Numbers (8 Ways)
• How to Remove Letters from Cell in Excel (10 Methods)
• Remove Specific Text from Cell in Excel (Easiest 11 Ways)
• How to Remove Text before a Space with Excel Formula (5 Methods)