Formula to reverse Last Name and First Name

[mi2slick]

Please how do i do the following

column a has a list of names in it - last name, first name

is there a way to reverse that to read - first name, last name

with out having to re type all names

[Richard Buttrey]

If there are only ever two names separated by a space then one way would be

Please Login or Register  to view this content.

However I can't help thinking that all the names may not be a simple first/last name, in which case we need to see a representative sample of your data.

[mi2slick]

MR. bUTTREY THANKS THAT DIDNT HELP I HAVE ATTACHED A SAMPLE

[torachan]

@Richards solution works with the adaption to accomodate ','

Please Login or Register  to view this content.

[jeffreybrown]

You might need to either add a -1 to the formula or wrap in a Trim

=RIGHT(A2,LEN(A2)-FIND(",",A2)-1)&" "&LEFT(A2,FIND(",",A2)-1)

=TRIM(RIGHT(A2,LEN(A2)-FIND(",",A2))&" "&LEFT(A2,FIND(",",A2)-1))

[Ace_XL]

Aesthetically speaking..

=TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",255)),255))&" "&TRIM(LEFT(SUBSTITUTE(A2,",",REPT(" ",255)),255))

or

TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",255)),255)&" "&LEFT(SUBSTITUTE(A2,",",REPT(" ",255)),255))

[Phuocam]

Or try:

=MID(A2&" "&A2,FIND(",",A2)+2,LEN(A2)-1)

[Phuocam]

Another way:

=TRIM(MID(SUBSTITUTE(A2&" "&A2,",",REPT(" ",255)),255,255))

[alansidman]

An alternative solution if you are using Excel 2010 or later is Power Query. Here is the Mcode and the document

Please Login or Register  to view this content.

Since you posted an .xlsx file, I can assume you are no longer using Excel 2003 as indicated in your profile. Please update your profile to the correct version of excel. This is important as solution may vary based upon the version you are using.

[mi2slick]

Thanks all got it to work

happy new year all the best for 2020

[jeffreybrown]

Glad to see these solutions worked for you. You're very welcome. We are happy to help and thanks for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Richard Buttrey]

The other pragmatic solution would have been to use TextToColumns specifiying the comma as a delimiter then in column C concatenate the names with

Please Login or Register  to view this content.