Out of practice

I pretend to myself that I used to be fair to middlin' at Excel.  Maybe, years ago, but I'm sure not any longer.

A project the wonderful wife is working on had first and last names in a single column.  I wanted to alphabetize, by last name  and to do that I wanted to create a column with last name first and first name last.  I'd be embarrassed to tell you how long it took.  Suffice it to say I could have retyped all the names much faster. Several times over.

Anyway, after spinning my wheels for a while and then kludging together something that sort of, kind of, worked, I decided to see how I should have done it, so I Googled "excel last name first" and found a clever solution.

Suppose the original column begins in cell A1 with the name John Doe, and we want the reformatted named (Doe John) to begin in B1.  Enter the following formula into B1:
=MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1))

The third term in parentheses  -- LEN(A1) -- calculates how many characters are in "John Doe" (8)
The second term -- FIND(" ",A1)+1 -- counts how many characters there are up to and including the space in "John Doe" (5) and adds 1 to that number (6)
The third term  -- A1&" "&A1 -- is the clever part.  It creates the string "John Doe John Doe" and, starting at character 6, selects 8 characters.  Voila -- "Doe John"!

To convert all the other names in column A, of course, simply copy the formula in B1 and paste it into the other rows in B.

Footnote 1: Actually, I wanted a comma and a space between the last and first names, not just a space, so I wound up using A1&", "&A1 as the first term in the formula and LEN(A1)+1 as the third.

Footnore 2: The formula works for John Doe, Jane Roe, Barack Obama, and Dan Larkins, but not for John Paul Jones, Dwight David Eisenhower, Billy Bob Thornton, and Marie-Joseph Paul Yves Roch Gilbert du Motier de Lafayette.

Footnote 3:  Want a simple way to insert a blank row into every other row of a table?  No problem.