How to Remove Specific Text from a Cell in Excel Using a Formula?

Sharing is caring!

Have you ever needed to remove certain text or characters from cells in Microsoft Excel? Perhaps you have a spreadsheet with full names but only want the first names. Or maybe you need to extract a substring, like a product ID number, from a longer string of text. The good news is that you can easily remove specific text from cells in Excel using formulas. In this article, we’ll show you several methods to delete unwanted text from your Excel data.

Using the SUBSTITUTE Function to Remove Text

One of the easiest ways to remove specific text from cells is by using Excel’s built-in SUBSTITUTE function. The SUBSTITUTE function searches for a substring within a text string and replaces it with different text (or nothing at all to effectively delete it). Here’s the syntax:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

• text – The text or reference to a cell containing the text you want to modify
• old_text – The substring you want to remove
• new_text – The text you want to replace the old_text with (leave this blank “” to delete the text)
• [instance_num] – Optional. Specifies which occurrence of old_text to replace. If omitted, all occurrences are replaced.

Examples of Using SUBSTITUTE to Remove Text

Let’s look at a few examples of using SUBSTITUTE to remove unwanted text:

Suppose you have full names in column A and want to extract just the first names in column B. You could use this formula in B2:
=SUBSTITUTE(A2,” “&RIGHT(A2,LEN(A2)-FIND(” “,A2)),””)

This removes everything after the first space, leaving just the first name.

Or let’s say you have product codes like “PROD1234” in column A and want to remove the “PROD” prefix in column B. Use this in B2:
=SUBSTITUTE(A2,”PROD”,””)

To remove unwanted characters like hyphens from phone numbers:
=SUBSTITUTE(A2,”-“,””)

As you can see, the SUBSTITUTE function provides a quick way to find and remove specific text or characters from your cells.

Combining SUBSTITUTE with Other Functions

For more advanced text parsing and extraction, you can combine SUBSTITUTE with other Excel functions like FIND, LEN, LEFT, RIGHT, and MID.

Using SUBSTITUTE with FIND and LEN

The FIND function locates the position of a substring within text. The LEN function returns the length of a text string. Together with SUBSTITUTE, you can use them to remove everything before or after a certain substring.

For example, to remove everything after the first comma in a cell:
=SUBSTITUTE(A2,RIGHT(A2,LEN(A2)-FIND(“,”,A2)),””)

This finds the position of the first comma, calculates the length of the text after it using LEN, and then removes that trailing text with SUBSTITUTE, leaving everything before the comma.

Using SUBSTITUTE with LEFT, RIGHT, and MID

The LEFT, RIGHT and MID functions return a specified number of characters from the start, end, or middle of a text string respectively.

For example, to extract the first 3 characters of a cell:
=LEFT(SUBSTITUTE(A2,”-“,””),3)

To get the last 5 characters:
=RIGHT(SUBSTITUTE(A2,”-“,””),5)

And to retrieve 4 characters starting from the 2nd position:
=MID(SUBSTITUTE(A2,”-“,””),2,4)

Combining these functions with SUBSTITUTE allows you to remove unwanted characters and then extract a substring from a specific position.

Removing Line Breaks with SUBSTITUTE

Another common scenario is removing line breaks within cells. Line breaks are represented by the ASCII characters CHAR(10) for line feed (LF) and CHAR(13) for carriage return (CR).

To remove all line breaks from a cell:

=SUBSTITUTE(SUBSTITUTE(A2,CHAR(10),””),CHAR(13),””)

This nested SUBSTITUTE formula first removes LF and then CR to delete any line breaks.

Using Power Query to Remove Text

For more complex text transformations across an entire data set, you can use Excel’s Power Query tool (called “Get & Transform” in Excel 2016 and later).

With Power Query, you can create a repeatable query that modifies your data, including removing unwanted text. The changes are applied dynamically, so your worksheet updates automatically if the source data changes.

Here’s how to use Power Query to remove specific text:

1. Select any cell in your data table.
2. Go to the Data tab and click From Table/Range to create a new Power Query query.
3. In the Power Query Editor, select the column with the text you want to change.
4. Go to the Transform tab and click Replace Values.
5. Enter the text to find and replace with (leave blank to remove it), then click OK.
6. Click Close & Load to apply the changes and load the modified data into a new worksheet.

Power Query saves the steps, so if your source data updates, simply right-click the query and Refresh to reapply the text removal.

Removing Text with Flash Fill

For simple text extractions, you can also try Flash Fill, a feature that automatically fills data based on patterns it detects in your examples.

To use Flash Fill to remove unwanted text:

1. In the column next to your data, type an example of how you want the text to appear, e.g. with certain characters removed.
2. Start typing a second example in the next cell below.
3. When you see Excel display a preview of the filled data, press Enter to accept it.

Excel will detect the pattern and fill the remaining cells, removing the unwanted text you excluded from your examples. Flash Fill works best for simple, consistent patterns rather than complex string manipulations.

Considerations for Removing Text Across Worksheets

If you need to remove text from multiple worksheets, you can’t simply copy the formulas across because the worksheet name is part of the cell reference.

Instead, use the INDIRECT function to create a dynamic reference that automatically updates for each sheet.

For example, to remove hyphens from cell A2 on every worksheet:

=SUBSTITUTE(INDIRECT(“‘”&A1&”‘!A2″),”-“,””)

Where A1 contains the name of the worksheet. Copy this formula down and across all the cells where you need to remove the text. The INDIRECT function will update the sheet reference for each row.

Final Thoughts

As you can see, there are several ways to remove unwanted text from cells in Excel. For simple substring removal, the SUBSTITUTE function works well on its own.
Combining it with FIND, LEN, LEFT, RIGHT, and MID enables more advanced text parsing and extraction. To modify entire columns, look to Power Query for a repeatable text-removal solution. And for straightforward text patterns, Flash Fill can often get the job done with a few examples.

By mastering these techniques, you’ll be able to clean and manipulate text in Excel efficiently. Whether you need to remove extra characters, extract key details, or standardize inconsistent data, these formulas and tools will help you get your worksheet data looking exactly how you need it.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.