We have a list of names…
… but we need to sort the list by the last name. To do this, we would need to separate the first and last names into separate columns.
Without using a variety of text manipulation functions like Left, Mid, Right, Len, Search, Find, Replace, etc., we can use SmartFill.
The key here is to provide an example of how you WISH your data were structured.
In the column directly to the right of the data, click the cell next to the first entry in the list and type the first name. Next, type the first name of the next entry in the list.
NOTE: Depending on the data, you may be required to provide between 2 and 6 examples for SmartFill to determine the needed strategy to fulfill the request.
Sheets’ SmartFill has presented us with a list of what it thinks we would like to continue if we were typing the entire list manually.
To see the underlying formula that has been created to achieve such a result, click the Show Formula link.
This displays the formula that will be used to generate the results.
To accept the results, click the Green Check button or press CTRL-Shift-Y. The cells have been filled with the needed formula.
Take a look at the formula you DIDN’T have to write.
=LEFT(A1, FIND(" ", A1) - 1)
To create the list of last names, repeat the process but by typing a few last names as an example.
The formula for generating the last names is not one that most people would enjoy writing.
=RIGHT(A1, LEN(A1) - (FIND(" ", A1) ) )