Converting name formats

[bateswz]

I need an excel formula to do the following name conversion:

Current format: Tom & Nancy Smith
Desired format: Smith, Tom & Nancy

I have seen other examples that work with only one first name and one last name, but none for a couples first names.

Thanks in advance, bateswi

 

[Swapnil Shah]

Try following assuming you have your input data in cell A2

=right(A2,len(A2)- find(" ",A2,find("&",A2,1)+2))&", "&left(A2,find(" ",A2,find("&",A2,1)+2)-1)

Thanks
Swapnil Shah

 

[bateswz]

Swapnil, this worked great!! However, can you modify this formula to also handle this formatting need as some of the names appear like this for singles:

Current format: John Smith
Desired format: Smith, John

Thanks in advance, bateswi

 

[Swapnil Shah]

Hi ,

Please find below
=if(isnumber(find("&",A2,1)),right(A2,len(A2)- find(" ",A2,find("&",A2,1)+2))&", "&left(A2,find(" ",A2,find("&",A2,1)+2)-1),right(A2,len(A2)-find(" ",A2,1))&", "&left(A2,find(" ",A2,1)-1))

Thanks
Swapnil Shah

 

[Peter_SSs]

Welcome to the MrExcel board!

The success of this type of problem depends on the uniformity of your data. For example, could you have

- A single person with 2 first names (Mary Lou Grant)

- A single person with a multi-word last name (John von der Borch)

- A single person with both of the above ((Mary Lou von der Borch)

- A couple but written like this (Tom and Nancy Smith)

- More than 2 people ( John, Geoff & Tim Jones)



However, for the 2 examples that you have given, you could try this, copied down.

Excel 2016 (Windows) 32 bit
	A	B
1	Tom & Nancy Smith	Smith, Tom & Nancy
2	John Smith	Smith, John
Rearrange names

Cell Formulas
Range	Formula
B1	=LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),50))&", "&A1,LEN(A1)+1)

 

[Rick Rothstein]

Swapnil, this worked great!! However, can you modify this formula to also handle this formatting need as some of the names appear like this for singles:

Current format: John Smith
Desired format: Smith, John

Any chance your names can include middle names or middle initials?

 

[bateswz]

Peter, this formula took care of 95% of my name formatting needs. The only quirks I had were names like the following:
Format before applying formula: Bill & Nancy Smith, III
Format after applying formula: III, Bill & Nancy Smith,

In response to a question Swapnil raised, my list does not contain any middle names or initials.

Thanks for all of your help, bateswi

 

[mole999]

and then there are mixed named couples, the variations are endless unless you can store each fraction of a name in cells and assemble as required to display

 

[Rick Rothstein]

Peter, this formula took care of 95% of my name formatting needs. The only quirks I had were names like the following:
Format before applying formula: Bill & Nancy Smith, III
Format after applying formula: III, Bill & Nancy Smith,

I have modified Peter's formula to handle names where there was a comma and a space followed by things like Jr., Sr., MD, III, and other single "words" with no spaces in them. See if this increase its applicability to a noticeable amount more than 95%.

=SUBSTITUTE(LEFT(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,", ",",")," ",REPT(" ",50)),50))&", "&SUBSTITUTE(A1,", ",","),LEN(SUBSTITUTE(A1,", ",","))+1),",",", ")

 

[Peter_SSs]

Peter, this formula took care of 95% of my name formatting needs.

That's not too bad. As has been stated/implied several times in this thread, you are unlikely to get 100% on this sort of job unless your data is very uniform.

The only quirks I had were names like the following:
Format before applying formula: Bill & Nancy Smith, III
Format after applying formula: III, Bill & Nancy Smith,

Unfortunately you did not finish that with
Format I would like for this name: .....

However, if the format is as Rick has guessed "Smith, III, Bill & Nancy" then I would make this simpler adjustment to my formula, also with the provisos re single words & spaces that Rick mentioned.

=LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),IF(ISNUMBER(FIND(",",A1)),100,50)))&", "&A1,LEN(A1)+1)