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
Table of Contents
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:
- Copy all the Dirty data
- Open a new Microsoft Word Document
- Paste the data there
- Click Ctrl + H
- Go to Special and Select Any Letter
6. Copy the clean data and paste it back to Excel.