Google Sheets: Cleaning data

Step 1
This table shows the result of importHTML. In this form, any changes to the data source (the Wikipedia page) will automatically be reflected here, and are updated at least once an hour. However, we can’t edit the values in the cells to remove undesired characters. We will use paste special in Google Sheets to create a static snapshot of the data. With this, we will lose the ability to update the table automatically via importHTML, but we will be able to edit it.

Step 2
Select all of the data by left-clicking in the top left rectangle in your sheet. Once all cells are highlighted, click Edit > Copy. Select Edit > Paste special > Paste values only.  We’re now able to edit the table.

Step 3
To make editing easier, we’ll freeze the row with the names of the columns. Hover the mouse cursor to the line just above row 1  over the gray bar. You will notice the cursor turns into a glove. Drag the bar to the bottom of row 1 and leave it there. Now the top row is frozen.

Step 1
Since we don’t need column F for this exercise,   right-click on the letter F at the top of the column and select Delete.

Step 2
There is a letter “F” next to number 13 in row A14, and a “TS3” next to number 4 in cell B27. We will remove these characters so that only the numbers 13 and 4 remain.

Step 3
Remove the extra letters in cells B40 and B48, so that only 19 and 8 remain. Do the same in D17 to remove the leading “F8”.

Step 1
Select column C by left-clicking on the letter C at the top of the column. Select Edit > Find and replace.

Step 2
In the first text box type the asterisk symbol: * (that’s the character we want to find in column C). Leave the Replace with text box empty so that the asterisks get replaced with nothing, which means they will be deleted.

Step 3
Make sure the Search option says Specific range and the range reflects the column you just selected. Leave the checkboxes unchecked.

Step 4
Select Replace all. Notice Google Sheets will tell you it Replaced 100 instances of * with (nothing). That means you successfully removed 100 characters in 50 rows with just a few clicks!

Step 5
Select Done. Our table is now clean and ready for us to work with. In the next lesson, we will  produce visualizations and get insights from the data.