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

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 colon(:) in our data. To replace the second hyphen(-) with colon(:) in our example, use this process. Enter this data in cell B5: “ADC-25:586”. We have replaced the second hyphen(-) with 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.

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

Removing text from a column using Flash Fill.

Read More: Data clean-up techniques in Excel: Filling blank cells

Another example, say we want to remove the second hyphen(-) from our data. To replace the second hyphen(-) from our data, in 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.

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

Removing text from data using 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.

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

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 🙂

Read More…

Data clean-up techniques in Excel: Adding text to cells

Data clean-up techniques in Excel: Fixing trailing minus signs

Data clean-up techniques in Excel: Adding text to cells

Data clean-up techniques in Excel: Randomizing the rows

Data clean-up techniques in Excel: Classifying values


Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

We will be happy to hear your thoughts

      Leave a reply