Extract last name and first name, separated by comma (from full name with 3 or more names)

[valmir]

Hi everyone:
I have found many posts about extracting the last name, first name and last and first, all from a full name with 2 names only. What if the full name has more than two names? Is there a formula that works for both full names with two names and full names with for example, 3, 4 or even more names?
If there is a way, I would like the name to be separated by a comma. Examples:
John Lauren Smith becomes Smith, John
Mary Scott becomes Scott, Mary
Paulo Teodoro Almeida da Silva becomes Silva, Paulo
Thanks!

 

[alansidman]

Because you are using 365, you can use Flash Fill. Here is a link to a tutorial

Video: Use AutoFill and Flash Fill - Microsoft Support

Training: Automatically fill data in your worksheet, like dates, numbers, text, and formulas. And Flash Fill recognizes patterns in your data and fills out the rest for you.

support.microsoft.com

 

[valmir]

Thanks a lot for the tip! So simple and I didn't even have an idea this feature existed!

 

[valmir]

Hi Alan! I ran into a small problem. I have non adjacent columns so it looks like autofill only works for adjacent columns

 

[valmir]

For those who may run into the same problem (auto complete or auto fill as suggested does not work for non adjacent columns, try this formula. (In this case, your source data is in cell A1):
=TRIM(PROPER(RIGHT(SUBSTITUTE(TRIM(A1);" ";REPT(" ";255));255)))&", "&TRIM(PROPER(LEFT(A1;SEARCH(" ";A1))))