#StandWithUkraine - Stop the Russian invasion

Join us and donate. We are contributing all book royalties from 2022 to present to:
Save Life in Ukraine and Ukraine Humanitarian Appeal.

Find and Replace with Blank

One of the simplest and most powerful cleanup tools inside every spreadsheet is the Find and Replace command. You can also use it to bulk-change different spellings of the same name, such as shortening a country’s name (from Republic of India to India), or expanding a name (from US to United States), or translating names (from Italy to Italia). Also, you can use find and replace with a blank entry to remove units of measurement that sometimes reside in the same cells as the numbers (such as changing 321 kg to 321).

Let’s look at Find and Replace in practice. A common problem with US Census data is that geographic names contain unnecessary words. For example, when you download data on the population of Connecticut towns, the location column will contain the word “town” after every name:

Hartford town
New Haven town
Stamford town

But usually you want a clean list of towns, either to display in a chart or to merge with another dataset, like this:

Hartford
New Haven
Stamford

Let’s use Find and Replace on a sample US Census file we downloaded with 169 Connecticut town names and their populations, to remove the unwanted “town” label after each place name.

  1. Open the CT Town Geonames file in Google Sheets, sign in with your account, and go to File > Make a Copy to create a version you can edit in your Google Drive.

  2. Select the column you want to modify by clicking its header. If you don’t select a column, you will be searching and replacing in the entire spreadsheet.

  3. In the Edit menu, choose Find and replace. You will see the window like is shown in Figure 4.3.

  4. In the Find field, type town, and be sure to insert a blank space before the word. If you do not insert a space, you will accidentally remove town from places such as Newtown. Also, you’ll accidentally create trailing spaces, or whitespace at the end of a line without any other characters following it, which can cause troubles in the future.

  5. Leave the Replace with field blank. Do not insert a space. Just leave it empty.

  6. The Search field should be set to the range you selected in step 2, or All sheets if you didn’t select anything.

  7. You have the option to match case. If checked, town and Town and tOwN will be treated differently. For our purpose, you can leave match case unchecked.

  8. Press the Replace all button. Since this sample file contains 169 towns, the window will state that 169 instances of “town” have been replaced.

  9. Inspect the resulting sheet. Make sure that places that include town in their name, such as Newtown, remained untouched.

Find and Replace window in Google Sheets.

Figure 4.3: Find and Replace window in Google Sheets.