How to Clean Data in Excel -- Search And Replace

One of the best tricks to clean up data is the Search/Replace function in Excel. The feature allows you to find a specific word and Replace it with a new one. This is particularly useful when different spelling variations appear for the same word such as ‘non-profit’ and ‘nonprofit’ or ‘California’ and ‘CA’. 

You can find this function under Edit -> Replace. Alternatively you can use the keyboard shortcut Control+F (Command+F on Mac) and click ‘Replace’.

Simply type in the word you are looking to replace in the top entry field and the word you want to replace it with in the bottom entry field and click ‘Replace All’. That’s how easy it is. 

Notice you can select whether you want to replace entries only in the sheet you have selected or across the entire workbook. You can also select a range of cells and only have the function replace the words in that range. 

Bonus tip: The Replace function is also an easy way to delete extra spaces in cells. Just add a space in the top entry field and nothing in the bottom entry field and click ‘Replace all’, all extra spaces in your selected range will be deleted. 


Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request