Changing LastName, FirstName XX cell data to FirstName LastName XX

Hey guys,

I'm sorry if this has been answered before, but I wasn't able to find any that worked for this situation.

I have about 4,000 names in column A that are currently in the format LastName, FirstName XX (XX stands in place of a two-character job title abbreviation, and as such is different from row to row; not how I would have done this, but it is what it is).

I need to figure out a way to change these cells to FirstName LastName XX. It makes no difference to me if this is accomplished by initially splitting the data into individual columns and then reassembling, or if it's all done with a single command.

To further complicate matters, there are more than a few entries in here with multiple first names (i.e., Smith, Mary Beth NP).

I've found formulas in the forums that have gotten me fairly consistently to FirstName XX LastName, but can't get over the hump to the fully correct format.

Any help you guys can offer would be greatly appreciated. I've been given a friendly challenge that I'll be unable to sort this out before the morning, and I'd really love to have it corrected and in their inbox by morning.

Thanks again, everyone.

Mike

Originally Posted by Ron Coderre

With
A1: Public, John Q.

To display the name as Firstname Lastname
B1: =MID(A1&" "&A1,FIND(",",A1)+2,LEN(A1)-1)
or
If extra spaces may be a problem:
B1: =TRIM(MID(A1&" "&A1,FIND(",",A1)+2,LEN(A1)-1))

Returns John Q. Public

Does that help?

Ron,

Thank you very much for your reply.

This returns FirstName XX LastName, which is close, but not quite there.

The XX isn't a middle initial, but a two-character job title abbreviation, and must remain at the end of the name, so the desired output is FirstName LastName XX.

EDIT: So to clarify, with A1: Public, John NP, I'm looking for output John Public NP.

Thank you again; I really do appreciate you giving me a hand with this.

-Mike.

Originally Posted by Ron Coderre

I just re-read your post and saw something I missed the first time.

With
A1: Public, John Q. XX

To display the name as Firstname Lastname XX
try this:

Please Login or Register  to view this content.

In the above example, the formula returns: John Q. Public XX

Does that hep?

Ron,

This worked absolutely perfectly. I was expecting to need to manually rearrange those entries with multiple first names, but your code even parsed those correctly.

I can't tell you how much I appreciate you taking the time to help me with this; I'm extremely grateful.

-Mike.

This worked perfectly, once I realized B5 was looking for data in A1. What a great time saver and it made me look like a real smart guy....someday I will tell the truth but the HR gal thought "cheeky forum moderator" was downright decent. Thanks for all you do.