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

Solution 1: Using Find and Replace Dialog box.

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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.

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

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.

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo