Changing Firstname Lastname to Lastname, Firstname

[jm594]

My Column A has names in this format Lastname, Firstname and this is the desired format. For example a name like Doe, John or Adams, Sally is perfect and my desired format. Unfortunately, interspersed through all these properly formatted names I have names that read Firstname Lastname such as Bob Bradock or Chelsea Smith.

How can I write an equation/function that I can copy down the next column that will give me the properly formatted names but also find the names that have Firstname Lastname and somehow change it to Lastname, Firstname.

Thank you.

[Richard Buttrey]

Hi,

This is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

[hrlngrv]

Isn't this particular question so old and oft asked & answered that anyone familiar with the problem would have added the usual caveats?

In this particular case, if there's a comma in a cell containing a name, it'd need to be assumed that it was already in surname, given name order? If not, that's where the real effort would be needed? Specifically how to handle the presence of middle names and multiple word surnames, e.g., de la Hoya or (my favorite Native American surname) Kills Man with Six-Gun.