If you’re like me, you get some really messy data in Google Sheets. I have found some very easy ways to clean up this data and make your sheets look really nice. In this Tech Tip, I will focus on three topics – split columns, CONCAT and CONCATENATE, and removing text using the find and replace feature.
Before I get started, I do want to remind you that Google Sheets has TONS of help built in. Under the Help menu, click Function list and you will be taken to a searchable list of functions that are built-in to Sheets. For each function, there is the syntax and a link to learn even more.
Also, when entering a function in a cell, click the question mark icon to bring up a quick help that will walk you through the entire process.
OK, let’s get started.
SPLIT COLUMNS
Have you ever needed to split up text that is all together, like maybe you have a field with the full name and you need the first and last name split out? You can use the built-in split columns feature to do this. It’s simple to use. Just highlight the column you wish to split, and then choose Split text to columns… from the Data menu.
Choose your separator (or let it auto-choose for you) and you will now have your data in multiple columns.
You can then drag and drop your columns to order your Sheet however you like. Now, how about the reverse?
CONCAT and CONCATENATE
These two functions each put things together, but they work very differently. CONCAT works with only two items. So if you have data in two cells, you can use CONCAT to put them together. Keep in mind that there will be no spaces allowed. Say you have data in cells A1 and B1. In C1, you can use the function =CONCAT(A1,B1) to combine the two items.
You can also use CONCAT to append something to data. For example if you have a list of email usernames in A1 and need to add the rest of the address to each name, you could use a formula like this to build the email address.
Note that any time you use text within a formula, you need to remember to put it in quotation marks. Plus, check out that sweet little preview of your results in Sheets.
CONCATENATE takes things one step further because you can combine more than two things. So, if we wanted to take columns with the first and last name and make them one combined name, we can use CONCATENATE like this. Again, remember your quote marks.
REMOVING TEXT
Use the built in Find and Replace feature to find text throughout your sheet (and replace it if you so choose). To get to it, go to the Edit menu and then Find and replace. You can also use the keyboard shortcut.
Search for the text you want, and then replace it with something else. If you want to replace text with a blank, just leave the Replace with box blank. This is great for cleaning up stray characters in your document.
I think that Google Sheets is my favorite of all the G Suite apps. Hopefully, these simple tips help you get control of your data in Sheets.