Data clean-up techniques in Excel: Replacing or removing text in cells

 

Table of Contents

Solution 1: Using Find and Replace Dialog box.

Solution 2: Using Flash Fill

Solution 3: Using SUBSTITUTE Formula

Solution 1: Using Find and Replace Dialog box.

Sometimes it is necessary to replace or remove some characters from a column of data. For example, you may need to replace all backslash characters with forward slash characters. I most cases, you can do this using Excel’s “Find and Replace” dialog box. This dialog box appears when you choose: Home ➪ Editing ➪ Find & Replace ➪Replace.

To remove text using the “Find and Replace” dialog box, just leave the “Replace With” field empty.

Solution 2: Using Flash Fill

You can use Excel 2013’s new feature Flash Fill to remove or replace text from a column.

Download this file to work with replacing and removing text from a column.

For example, we want to replace the second hyphen(-) with a colon(:) in our data. To replace the second hyphen(-) with a colon(:) in our example, use this process. Enter this data in cell B5: “ADC-25:586”. We have replaced the second hyphen(-) with a colon(:).

Then start writing B in cell B6, Excel will automatically show you suggestions. Press Enter, your data will be changed for the whole column.

If for some reason, Excel does not show any suggestion, press CTRL+E to active Flash Fill.

Removing text from a column using Flash Fill.

Another example, say we want to remove the second hyphen(-) from our data. To replace the second hyphen(-) from our data, in the example, use this process. Enter this data in cell B5 "ADC-25586" .

We have replaced the second hyphen(-).

Then start writing B in cell B6, Excel will automatically show you suggestions. Press Enter, your data will be changed for the whole column.

If for some reason, Excel does not show any suggestion, press CTRL+E to active Flash Fill.

Removing text from data using the Flash Fill technique.

Solution 3: Using SUBSTITUTE Formula

In other situations, you may need a formula-based solution. Consider the data shown in our previous example.

Our goal is to replace the second hyphen(-) character with a colon(:). Using “Find and Replace” wouldn’t work here, because there is no way to specify only the second hyphen in this process.

We can use a simple formula that replaces the second occurrence of a hyphen with a colon. Enter this formula in cell B5: =SUBSTITUTE(A5, “-“, “:”, 2). Copy this formula for the cell from B6 to B13.

Replacing text in Excel using Substitute function.

To know details about SUBSTITUTE() Function click this link: SUBSTITUTE Function in Excel.

To remove the second occurrence of a hyphen, just omit the third argument for the SUBSTITUTE function:
=SUBSTITUTE(A5, “-“, , 2).

Happy Excelling 🙂