excel

[Guest]

Documents one and two have a list of names
On document one the name is listed as Doe, John
On document two the name is listed as John Doe
I would like to reverse the name on document two to read as Doe John so that I may do a lookup for it on document one
Is there a formula for this option?

 

[JE McGimpsey]

One way:

=MID(A1,FIND(" ",A1)+1,255) & ", " & LEFT(A1,FIND(" ",A1)-1)

 

[David McRitchie]

You can do something with a worksheet function, but I'd really
recommend a macro as the best way to go so that you can
better process John F. Smith Ron de Bruin and your John Doe
see Chip Pearson's site: http://www.cpearson.com/excel/FirstLast.htm

If you want to be safer from your standpoint you might
consider normalizing the one with the comma in your sheet1
and in your table then you don't have to worry about where the
separation is as it's actually been determined for you.
You would want to retain the separation though somewhere.