How to Find and Replace ANYTHING in Excel

Data cleaning takes up to 80% of the time for a data analyst. To save time use the below tricks to find and replace mixed digits and letters in a string

Find and Replace ALL Numbers in Excel

Given the below data, find and replace all number digits

NB: the Trick is using the MIcrosoft WORD Special characters.

There are special cases where you are required to replace a specific digit.

For example replacing ONLY zeros with Blank

Zeros can mess with your analysis, especially where the value in the cell is supposed to be Blank.

For example in below data, if you check for the minimum cost, it will return 0 while it is supposed to be 10

NB: To invoke the Find and Replace Dialogue, Press Ctrl + H

If you try the simple find and replace zero, it replaces all zeros including those in tens, hundreds, thousands e.t.c

• Instead of replacing ONLY the 2 cells with absolute 0 it has replaced all zeros… the correct way is;
• Click Options, tick Match Entire cell content, and finally Replace All

NB: If you want to do a Case Sensitive Find and Replace, Click Match Case

Find and Replace ALL LETTERS in Excel

To replace all letters once follow the steps below:

1. Copy all the Dirty data
2. Open a new Microsoft Word Document
3. Paste the data there
4. Click Ctrl + H
5. Go to Special and Select Any Letter

6. Copy the clean data and paste it back to Excel.