ExcelInsider Team
In Excel, it is common to deal with messy or imported data that includes unwanted text such as suffixes, prefixes, or tags within a cell. If you are cleaning email addresses, product IDs, or descriptive labels, knowing how to remove specific text can help your workflow and prepare data for analysis or presentation. Although this may seem a bit difficult at first but we will use some easy method that will make it very easy to execute.
To remove specific text from cell, follow these steps:
➤ Identify the delimiter or unwanted pattern in your data (e.g., “-”, “@”, specific word).
➤ Use Excel tools like Find & Replace. Type what you want to remove in the Find What box, keep the Replace with box empty and click Replace all button.
This article will cover multiple practical ways to remove specific text from cells in Excel. We will walk you through Find & Replace, LEFT+FIND formula, Flash Fill, and VBA Macro methods using multiple dataset scenarios.
Using Find and Replace Feature to Remove Specific Text from Cells
Excel has a built-in Find and Replace feature that can remove unwanted or specific text from cells. It is very helpful when we need to clean up unwanted text patterns such as suffixes or tags across multiple rows or columns. We usually use this method for basic cleanup tasks without needing formulas or coding.
We have a dataset that represents employee data with temporary status marked in the Employee Name and ID Code columns. We will use the Find and Replace method to clean the data by removing the unwanted section “(temp)” from names and “-temp” from ID codes.
Steps:
➤ Open your Excel file. We have column A (“Employee Name“), column B (“Department”) and column C (“ID Code“).
➤ Press Ctrl + H on your keyboard. This opens the Find and Replace dialog box.
➤ In the Find what: field, type the text you want to remove, for instance, type “-temp” if you are cleaning names or -temp for IDs. Leave the Replace with: field blank.
➤ Click Replace All. Excel will search through your selected range and remove the specified text from all matching cells.
➤ Now you will see all unnecessary text has been removed from all cells.
Applying Flash Fill to Remove Specific Text from Cell
Flash Fill is an Excel build in feature that automatically fills in values based on patterns as you start typing. It is good for quick data cleanup when there is a clear, repeatable format to the data transformation. We usually use Flash Fill when we want to remove or extract parts of text across multiple cells without writing complex formulas.
Here we have a dataset of inventory where each entry in column A includes extra characters (batch codes) that are not required for display or filtering. We will use Flash Fill, to quickly clean up the product names for easier reading and further processing.
Steps:
➤ Open your dataset or you can use it by downloading this demo dataset. We have product info in column A. We will store the cleaned product name in column B.
➤ Click on cell B2. Type the cleaned version of the text, excluding the unwanted part. For example, if A2 is “Shampoo#A123”, type “Shampoo” in B2.
➤ Move to cell B3. Start typing the cleaned version of the next product name (Conditioner), and Excel will now suggest the rest of the pattern.
➤ Press Enter to accept the Flash Fill suggestions. Excel will auto-complete the rest of the column following your pattern.
Note:
Flash Fill works best when the pattern is consistent across all rows. If some entries differ (e.g., multiple # or other symbols), Flash Fill may not work perfectly.
Use SUBSTITUTE Function to Remove Specific Text from Cells
The SUBSTITUTE function in Excel is used to remove specific text or characters from a cell. It is used when we need to strip a consistent word, symbol, or phrase from strings across a dataset.
We have a dataset that has a contact sheet where phone numbers are stored with a country prefix. That needs to be removed for data uniformity and to import into a system that accepts only the 10 digit format. Using the SUBSTITUTE function we will remove the text from every cell.
Steps:
➤ Open your dataset. We have Full phone number in column A. We will store the Formatted phone number name in column B.
➤ Click into cell B2, where you want the cleaned phone number to appear.
➤ Enter the formula:
=SUBSTITUTE(A2, "+1-", "")
This function is used to find +1- in cell A2 and replace it with an empty string (“”) and remove it.
➤ Press Enter. We will see only the cleaned number (e.g., 2025550187) in cell B2.
➤ Drag the fill handle (small square at the bottom-right of B2) down to copy the formula to the rest of the cells in column B.
Note:
SUBSTITUTE is case-sensitive, so make sure the text you want to remove is exactly as it appears.
Using REPLACE Function to Remove Specific Text from Cells
The REPLACE function in Excel helps us to remove or replace a specified number of characters from any position within a text string. We use this method when we know exactly where the unwanted text is located, such as removing prefixes, suffixes, or specific character ranges from cells.
We have a list of employee ID codes that include a prefix “EMP” followed by a 5-digit number (e.g., “EMP12345”). We want to remove the first three characters (“EMP”) from each ID using the REPLACE function.
Steps:
➤ Open up your dataset. We have Employee ID in column A and we will store the Cleaned Employee Number in column B.
➤ Click on cell B2 next to our original employee ID in cell A2 where we want the cleaned ID to appear and Enter the formula:
=REPLACE(A2, 1, 3, "")
This means: starting at character 1 in cell A2, replace 3 characters with an empty string “” (effectively removing the first three characters). Cell B2 will now show the ID without the “EMP” prefix (e.g., 12345).
➤ Use the fill handle on the bottom-right corner of cell B2 to drag the formula down through cells B3 to B11 to apply it to all employee IDs.
Make Custom VBA Function to Remove Specific Text from Cells
A custom VBA function is used to remove specific parts of text based on custom logic,such as removing everything before or after a certain delimiter or based on character position. This method is good when we are dealing with structured text (e.g., logs, product codes, or feedback) that includes extra, unwanted content that can not easily be removed using built-in Excel functions.
Steps:
➤ Open your dataset or you can use it by downloading this demo dataset. We have Raw feedback in column A. We will store the cleaned feedback name in column B.
➤ Press Alt + F11 to open the Visual Basic for Applications (VBA) Editor.
➤ In the VBA Editor, go to Insert > Module to add a new module.
➤ Paste the following VBA code into the module:
Function RemoveText(cell As Range, delimiter As String, Optional direction As String = "right") As String
Dim parts() As String
parts = Split(cell.Value, delimiter)
If direction = "right" Then
RemoveText = parts(1)
Else
RemoveText = parts(0)
End If
End Function
➤ Press Ctrl + S to save the workbook as a Macro-Enabled Workbook (.xlsm). Close the editor.
➤ In cell B2, type the following formula and click Enter:
=RemoveText(A2, ":", "right")
➤ Drag this formula down to apply it to the rest of the column.
Note:
➥ You must enable macros each time you open the workbook to use this function.
➥ If a cell doesn’t contain the delimiter, the function may return an error or unexpected value-handle such cases by adding If statements in VBA or using IFERROR in Excel.
Frequently Asked Questions (FAQs)
How do I remove specific text in an Excel cell?
Use Find & Replace or a formula to locate and remove a particular word, phrase, or character.
How to extract certain text from a cell in Excel?
Use the LEFT, RIGHT, or MID functions in combination with FIND or SEARCH to extract specific parts of the text.
How do I separate specific text from a cell in Excel?
Use Text to Columns under the Data tab or TEXTSPLIT function in Excel 365 to separate values based on a delimiter.
How to trim text in Excel without formula?
Use Flash Fill: manually type the desired result next to the first value, then press Ctrl + E to auto-complete the pattern.
Concluding Words
We have shown 5 methods through this article with step by step instruction. Removing specific text from Excel cells is simple and efficient with the right approach. Use Find & Replace, LEFT+FIND formula, Flash Fill, and VBA Macro as these methods can clean your data quickly by removing specific text.
