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.
   
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.
   After that, a pop-up appears to confirm your replacements.
6. Click OK.
   
7. Press the Close button to close the Find and Replace dialog.

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

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.
   
2. In the next cell of the column, type 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. 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.
   

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.

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.

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.

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.

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.
   
2. 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 the Save button to 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.
   The Macro dialog will pop up.
3. Select the macro “remove_text_after_character” > click 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.

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