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

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel remove text after character


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.
    Applying Replace command
  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.
    Find and Replace dialog boxAfter that, a pop-up appears to confirm your replacements.
  6. Click on OK.
    Confirmation pop up
  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.

Result of removing text after character

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.
    Type desired output in first cell
  2. In the next cell of the column, start typing the next expected result, i.e. text before the character.
    Insert desired output in second cellThis 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.
    Navigate to Flash Fill feature

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

Flash Fill removing text after 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.

Deleting all text after 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.

Removing text after N-th occurrence

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.

Removing text after last 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.
    Visual Basic from Developer tab
  2. Then, from the menu bar, click on Insert > select Module.Insert 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

    Insert code in Module window

  4. Click on the Save button to save the code.
    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.
    Click Macros from Developer tabThe Macro dialog will pop up.
  3. Now, select the macro “remove_text_after_character” > click on Run.
    Run the Macro

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

VBA macro removing text after first character


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

2 Comments
  1. i can’t thank you enough!! you’ve saved me mega hours of manually deleting words after a semicolon (;) in million cells that’ve I’ve been doing for ages!

    • Glad that we could help you. Don’t hesitate to ask if you have any more Excel related problems. Here, our experts will be happy to assist you.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo