Ima guess this has probably happened to you: you've got a giant list of full names in a spreadsheet, names like "Tina Popintosh". Unfortunately, you need to isolate just that person's first name or their last name. Maybe it's for a mail merge, say if you want to send out messages that read "Dear Tina,".
But all you have are full names, so you start doing some manual data entry, typing "Tina" into the First Name column, then tying "Popintosh" into the Last Name column. Then, you move onto the next name, all the while thinking: "I could imagine a robot doing this job."
That's your cue; whenever you can picture a robot doing a repetitive task in Google Sheets, it means there's probably a way to automate it. In this case, there are two ways.
The SPLIT Function
This function SPLITS the contents of a cell at a point (or points) that you specify. Like all Sheets functions, it requires pieces of information (arguments) that tell it how to do its job
In this example, I have some names in column A that I want to SPLIT, so I start by typing my function in cell B2. Let's go through what arguments SPLIT needs in order work. Feel free to follow along in the example spreadsheet.
- First you need text, the data that you want to split. In our example that will be cell A2.
- Next, you need the delimiter. This is the spot where you want Sheets to SPLIT your data up. In our case, we want to split our data at the space character, so we type a space in quotation marks: " ". The quotation marks are important, you always need to include these.
- Finally, there's the split_by_each argument, which is optional. I recommend you ignore it.
3. The MID function needs starting and stopping points for its scissors; where should it start cutting and where should it stop? Well, you want it to start after the space character (which, we've already used the FIND function to locate). In our example, we want to start cutting at the location of the space character (in cell G2), plus 1:
4. Finally, we need to tell MID where to stop cutting. This is the last character in your cell, or (wait for it) the LENGTH of your cell's text. We also have this number ready, calculated by the LEN function in cell H2:
5. Our formula is all set, so we can now hit Enter and copy it (and the LEN function in column H) down the column to get our last names:
FINAL IMPORTANT NOTE- You Need Values
You're not quite done at this point. Whether you used SPLIT or the LEFT-MID-FIND-LEN technique, you still have one more step to take.
These functions you've created will all continue to work just fine, but only as long as the data in column A doesn't change. If we get cavilier and decide to delete column A, thinking "Great, we don't need the full names anymore!", watch what happens to all of our hard work:
Ahhh! To prevent this from happening, you need to do one more thing. Highlight all of the first and last names that you've extracted, copy them (Ctrl + c) and then paste them as values (Ctrl + Shift + v). This will get rid of all the formulas you made, but it will preserve the results, the names, which is what you really want.