How to Remove Text After Character in Excel: 4 Easy Ways

Using Find & Replace Feature

  1. Select your data.
  2. Press Ctrl + H to get the Find and Replace dialog.
    Or, go to the Home tab > Editing group > Find & Select dropdown > Replace.
    Applying Replace command
  3. 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 Replace All.
    Find and Replace dialog boxAfter that, a pop-up appears to confirm your replacements.
  6. Click OK.
    Confirmation pop up
  7. Press the Close button to close the Find and Replace dialog.

It will remove all the text after the comma in your dataset.

Result of removing text after character

Note: Press Alt + H + FD + R keys to access the Find & Replace dialog box.

Using Flash Fill Feature

  1. Type the text manually in the adjacent cell in the same row before the specific character.
    Type desired output in first cell
  2. In the next cell of the column, type 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. Select all the cells where you want the expected results, including the cells where you have manually entered the expected output.
  5. Press Ctrl + E.
    Go to the Home tab > Editing group > Fill drop-down > Flash Fill.
    Navigate to Flash Fill feature

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.

Applying Excel LEFT Function

Method 1 – Removing All Text After a Character

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

Remove all the texts after a certain character.

Deleting all text after character

Method 2 – Removing Text After N-th Occurrence of a Character

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

It will give you the result of the truncated data without all the text after the second comma.

Removing text after N-th occurrence


Method  3 – Removing Text After the Last Occurrence of a Character

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

It will remove all the text after the last occurrence of a certain character.

Removing text after last character


Using VBA Macro

Method 1 – Copy and Save the Code

  1. Press Alt + F11 on your keyboard to open Visual Basic Editor.
    Go to the tab Developer > click Visual Basic to get the VBA editor.
    Visual Basic from Developer tab
  2. 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 the Save button to save the code.
    Save the code

Method 2 – Run the Code on Your Data

  1. Select your data.
  2. Press Alt+F8.
    Click on the Developer tab > Macros buttons from the Code group of commands.
    Click Macros from Developer tabThe Macro dialog will pop up.
  3. Select the macro “remove_text_after_character” > click 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.

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

Get FREE Advanced Excel Exercises with Solutions!
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

  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