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

TL;DR: To remove text after a character in Excel, follow the steps:

1. Select your data.
2. Press Ctrl+H. The Find and Replace dialog box will appear.
3. Write the character in the Find what field, and put an Asterisk (*) symbol next to the character.
4. Keep the Replace with box empty.
5. Press Replace All.

Removing text after a character in Excel helps maintain data consistency and accuracy. It simplifies data analysis and enhances data presentation by cleaning and formatting information effectively. This action ensures that your Excel data is ready for various tasks like calculations, sorting, and reporting.

In this article, you will learn 4 different methods to remove text after character in Excel.

Say, you have a dataset of some employees containing first name and last name separated by a comma. Using Excel features, you can remove the part after the comma and keep the first name only.

You can remove text after character with the following 4 methods:

Using Find & Replace Feature

The Find and Replace feature allows you to easily locate and replace specific values. In the Find and Replace tool, we have to use a wildcard character, namely the Asterisk(*) symbol to remove text after a certain character. The Asterisk(*) symbol can represent any number of characters.

Follow the steps below to remove text after a certain character using the Find and Replace feature:

1. Select your data.
2. Press Ctrl + H to get the Find and Replace dialog.
   Or, you can go to the Home tab > Editing group > Find & Select dropdown > Replace.
   
3. Now, in the Find what box, insert the character after which you want to remove the text and put an Asterisk (*) symbol after the character.
4. Keep the Replace with box empty.
5. Click on Replace All.
   After that, a pop-up appears to confirm your replacements.
6. Click on OK.
   
7. Finally, press the Close button to close the Find and Replace dialog.

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

Note: You can also press Alt + H + FD + R keys to access the Find & Replace dialog box.

Using Flash Fill Feature

You can also use the Flash Fill feature to remove text after a character in Excel. This is particularly useful when there is an easy-to-follow pattern. The Flash Fill feature can recognize the pattern and automatically fill in the cells. Here’s how:

1. Type the text before the specific character manually in the adjacent cell in the same row.
   
2. In the next cell of the column, start typing the next expected result, i.e. text before the character.
   This will make a pattern that the Flash Fill can follow easily.
3. Continue typing the next result if the Flash Fill fails to recognize the pattern.
4. Now, select all the cells where you want to have the expected results, including the cells where you have put the expected output manually.
5. Press Ctrl + E.
   Or, go to the Home tab > Editing group > Fill drop-down > Flash Fill.
   

Thus, the Flash Fill recognizes the pattern, removes the text after the specific character, and keeps the text before the character.

Note: The Flash Fill feature is available only in Excel 2013 and later versions; not available in the older versions.

Read More: How to Remove Text Between Two Characters in Excel

Applying Excel LEFT Function

Unlike “Find & Replace” and “Flash Fill” features, the LEFT function provides a more automated, flexible, and controlled approach when you need to remove text after a specific character. Moreover, Excel formulas can update themselves when data changes. So, using the LEFT function is more efficient in some cases.

Here are 3 different cases to remove text after a character in Excel using the LEFT function:

Case 1: Removing All Text After a Character

You can use a combination of LEFT and SEARCH functions to delete all text after a character in Excel. If you have the same character multiple times, it will remove the text after the first character.

To remove all text after a character, go through the following steps:

1. Select a cell.
2. Type the following formula: =LEFT(B5,SEARCH(",",B5)-1)
   Here, B5 refers to the input cell.
3. Press Enter.
4. Now, drag the Fill Handle tool down to copy the formula if you have more input cells.

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

Case 2: Removing Text After N-th Occurrence of a Character

To remove text after the N-th occurrence of a character, you can combine the SUBSTITUTE and  FIND functions with the LEFT function. This formula is helpful when you have to remove text after a certain character, but that character occurs multiple times in the string.

The generic formula to remove text after N-th occurrence of a character is: =LEFT(A1, FIND("#", SUBSTITUTE(A1, "your_character", "#", N)) - 1)

Here, your_character refers to the character after which you want to remove text. N refers to the occurrence of the character after which you want to remove text.

Let’s say, the comma has appeared twice in every cell. The target is to remove texts after the 2nd comma.

To remove text after 2nd occurrence of a character, follow these steps:

1. Apply the formula following formula in a cell: =LEFT(B5,FIND("#",SUBSTITUTE(B5,",","#", 2))-1)
2. Drag down the Fill Handle tool to copy the formula for the rest of the cells.

Finally, it will give you the result of the truncated data without all the text after the second comma.

Read More: How to Remove Everything After a Character in Excel

Case 3: Removing Text After the Last Occurrence of a Character

To remove text after the last occurrence of a character use the following formula created by using a combination of LEFT, SUBSTITUTE, and LEN functions. Here’s how:

1. Enter the following formula in a cell: =LEFT(B5,FIND("#",SUBSTITUTE(B5,",","#",LEN(B5)-LEN(SUBSTITUTE(B5,",",""))))-1)
2. Drag down the Fill Handle tool to copy the formula for the rest of the cells.

Thus, it will remove all the text after the last occurrence of a certain character.

Read More: How to Remove Letters from Cell in Excel

Using VBA Macro

The use of VBA macro requires programming knowledge. However, you will be provided with a VBA code here. You can simply copy it and use it. You can also customize the VBA code as per your requirements.

To remove text after character using VBA Macro follow the steps below:

Step 1: Copy and Save the Code

1. Press Alt + F11 on your keyboard to open Visual Basic Editor.
   Alternatively, go to the tab Developer > click on Visual Basic to get the VBA editor.
   
2. Then, from the menu bar, click on Insert > select Module.
3. Copy the following code and paste it into the opened module.

   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

   

4. Click on the Save button to save the code.
   

Step 2: Run the Code on Your Data

1. Select your data.
2. Press Alt+F8.
   Or, click on the Developer tab > Macros buttons from the Code group of commands.
   The Macro dialog will pop up.
3. Now, select the macro “remove_text_after_character” > click on Run.
   

After running the code, you will get the desired result in the adjacent right column.

Download Practice Workbook

You can download the workbook from here.

Conclusion

In this article, you 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. Have a nice day!

Frequently Asked Questions

Is there a quick way to remove text after a space or a hyphen?

To remove text after a space or a hyphen, use the formula =LEFT(A1, FIND(” “, A1) – 1)

Are there any Excel shortcuts for removing text after a certain character?

Excel doesn’t have a specific shortcut for this task, but creating a custom function or using a combination of functions can streamline the process.

Is it possible to remove text automatically when data changes?

Yes, using Excel functions can change data automatically when data changes. Unfortunately, other Excel tools are unable to change data in case of data changes.

Related Articles

• How to Remove Text from an Excel Cell but Leave Numbers
• How to Remove Specific Text from Cell in Excel
• How to Remove Text before a Space with Excel Formula

<< Go Back To Excel Remove Text | Data Cleaning in Excel | Learn Excel