Hands-On Data Visualization

Finding and Replacing with an Empty Space

Do you know that amazing tool hiding in every spreadsheet? It’s the “Find and Replace” command. Not only does it allow you to mass-edit different spellings of the same name, like shortening the name of a country (from Republic of India to India), or expanding it (from États-Unis to United States), or even translating names (from Italy to Italia), but you can also use “find and replace” with an empty entry to remove units of measurement that are sometimes found in the same cells as numbers (for example, changing 321 kg to 321).

Let’s see how it works in practice. A common issue with US census data is that geographic names often contain unnecessary words. For example, when you download data about the population of cities in Connecticut, the location column includes the word “town” after each name:

Hartford town
New Haven town
Stamford town

But generally, you want a clean list of the cities, whether it’s for displaying on a graph or merging with another dataset, like this:

Hartford
New Haven
Stamford

Let’s use “Find and Replace” on a sample of the downloaded US census file, containing 169 city names in Connecticut and their population, to remove the unwanted label “town” after each place name.

1. Open the “CT Town Geonames” file in Google Sheets, sign in to your account, and go to “File” > “Make a Copy” to create an editable version in your Google Drive.
2. Select the column you want to modify by clicking on its header. If you don’t select a column, you’ll perform the search and replace on the entire spreadsheet.
3. In the “Edit” menu, choose “Find and Replace.” You’ll see a window like the one shown in Figure 4.3.
4. In the “Search” field, enter “town” and make sure to insert an empty space before the word. If you don’t insert a space, you’ll accidentally remove “town” from names like “Newtown.” Additionally, you’ll create extra spaces at the end of a line without any following characters, which can cause issues in the future.
5. Leave the “Replace with” field empty. Don’t add a space. Just leave it blank.
6. The “Search in” field should be set to the range you selected in step 2, or “All sheets” if you haven’t selected anything.
7. You have the option to match case. If you check this option, “town”, “Town”, and “tOWn” will be treated differently. But in our case, you can leave this option unchecked.
8. Click on the “Replace all” button. Since this example file contains 169 towns, a window will indicate that 169 occurrences of “town” have been replaced.
9. Check the result on the obtained sheet. Make sure that places that include “town” in their name, like “Newtown”, haven’t been modified.

And there you have it! You’ve just learned a quick trick to clean up your data and get a clearer and easier-to-use list. For more tips and tricks on Google Sheets, you can take a look at Crawlan.com. Have fun!