Productivity Tip: Cleaning Data in Google Sheets
In this blog, we go over the following:
Remove Spaces with the TRIM Function
Capitalization with PROPER, UPPER, and LOWER Functions
Separate Words with SPLIT Function
Imagine sending out a customer feedback form. The form syncs and the results shoot into Google Sheets. Looking at the spreadsheet, the outcome seems quite sloppy and not everyone has the same proper capitalization or the format you would expect to see. So the following are a few functions of the solution. In the below table, you can see that there is a lot that can be done. There are spaces that can be deleted, along with improper capitalization, and the first and last names can be divided if necessary. These functions can turn a mess into a clean spreadsheet.
Remove Spaces with the TRIM Function
TRIM is a function in Google Sheets that removes all spaces from text except for single spaces between words. This can be useful when cleaning up data or text that has been copied and pasted from other sources. The TRIM function can be accessed by typing =TRIM( in the cell where you want the text to be cleaned up. Remember to close with a final close parenthesis.
Capitalization with PROPER, UPPER, and LOWER Functions
PROPER is useful for proper nouns, such as names of people or geographic locations. By pointing =PROPER at cell B2, you can make sure that the changes are made to the text that’s already been trimmed. =UPPER can be used if you’d like the words to be entirely capitalized, and =LOWER can be used to make the words lowercase. Refer back to the above image for an example.
Separate Words with SPLIT Function
With the SPLIT function, you can divide text (that's currently in one cell) into separate columns. To start, enter =SPLIT( select the cell to separate and follow by a delimiter (which is a string that follows) in this case, a space. Refer back to the above image for an example.
So now we know how to tidy up a disordered-looking spreadsheet that has a lot of data. When there are more spaces than necessary, we can use the TRIM function to take them out easily. To quickly capitalize the first letter of each word in a Google spreadsheet, use the PROPER function. Last but not least, dividing up columns can help with easy filtering and looking neat with the SPLIT function. Not only does this help save time, but visually looks orderly and uniform.