Greetings, friend! Today, I want to show you a time-saving technique in Excel and Google Sheets. Technical data people call it a form of “Data cleaning”, but I will just call it: “you have a bunch of text information and you want to extract pieces of that text for use elsewhere”. For example, you may have a list of names and you want to create separate columns for First name and Last name. Depending on how much Excel or Google Sheets knowledge you have, this task either takes hours, minutes, or seconds. I want it to take seconds for you. Don’t fasten your seatbelt, we will be done before we leave the parking lot!
We have a list of 51 names below:
For those of you who want to follow along without actively participating, I bet you are fun at parties (*rolls eyes*); here is what the data looks like:
We want to extract the first names and last names and place them in their corresponding columns, so that we can have the final product below:
My favorite thing about Excel and Google Sheets is everyone can have a different method to arrive at the same result. For example, to separate the names, we can use various text manipulation functions, such as: MID, LEFT, LEFT and FIND, RIGHT, RIGHT and FIND, Text to Columns, and Flash fill, etc. However, I believe Flash fill is the fastest method and I value your time, so let’s do this.
To use Flash Fill in Excel, we fill the first row to show Excel what we are trying to do. In this case, we type Amelia-Rose under First name and Hartman under Last name. Thereafter, we press ENTER to jump to the next row to handle the next name: Azeem Calhoun. Under the First name column, press Ctrl + E. Do the same for the Last name column and voilà! We are done!
Flash Fill in Excel in action
Flash Fill happens in a similar way in Google Sheets via Smart Fill. However, Google Sheets creates the formula used to manipulate the text for you (the formula appears in the formula bar), and sometimes requires a few more entries (2 to 6) before the Smart Fill kicks in. I realize this gives Google Sheets a bad rep, but the feature is much newer than Excel’s Flash Fill. I am sure they’ll figure it out soon. However, using a Google Sheet’s formula means if you change the Full name, the other columns will be updated automatically. Because Excel Flash Fills automatically, you would have to repeat the (albeit 1 second) operation when the data changes.
Smart Fill in Google Sheets in action
Google Sheets has a function called SPLIT that separates text based on a specified character or set of characters such as spaces, commas and so on.
Below is an illustration of how the SPLIT function works. Might look like gibberish, but in the given example, 3 separate columns within the same row would be created, containing “a”, “b” and “c” respectively. Try using SPLIT to separate a date into a day, month and year.
SPLIT creates what we call a dynamic array when used, as it causes new columns to be created based on the number of dividers or separators or delimiters detected in the text in question. In the illustration below, the divider is a space (notice the intentional space made in between the quotation marks):
Bookmark this article, or better yet, subscribe to this blog so that the next time someone dumps a messy spreadsheet—think phone numbers in the wrong format, full names instead of first and last names—on you, you’ll smile, think of me and send me Mobile Money.
Until next time. Have a blessed week!
©[year] Rights Reserved
