I don't know if this is even possible...

W

Will61768

I want to take a list that has in a column Firstname Lastname and what
want to do is covert that into Lastname, Firstname.

Is that even possible?

The Firstname Lastname are in the same column, I wasn't sure if tha
was clear.


Thanks for any suggestions you can offer
 
S

swatsp0p

You need to give us the specific layout of the text in the cell and
formula can be written to invert the text.

Is it in the format of:

John Smith
John J. Smith
JohnSmith
John,Smith
Mr. John Smith
Mrs. Jane Doe
...etc.?

Also, are all entries of the same format or do you have multiples:

John Smith
Mr. Alex Rightly
Mrs. A.J. Dripper
...etc.?

Now if we assume your data is in the "John Smith" format, this formul
will return "Smith, John":

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

If not, more info, please.

Good Luc
 
B

Bob Phillips

If it is just like that with a space separator, try

=MID(A1,FIND(" ",A1)+1,99)&", "&LEFT(A1,FIND(" ",A1))
 
A

Ashish Mathur

Hi,

You may try the following array formula (Ctrl+Shift+Enter)

=MID(A16,MATCH(TRUE,EXACT(MID(A16,ROW($2:$13),1),PROPER(MID(A16,ROW($2:$13),1))),0)+2,255)&"
"&MID(A16,MATCH(TRUE,EXACT(MID(A16,ROW($1:$13),1),PROPER(MID(A16,ROW($1:$13),1))),0),MATCH(TRUE,EXACT(MID(A16,ROW($2:$13),1),PROPER(MID(A16,ROW($2:$13),1))),0))

A16 contains the name as First name Last name.

Hope this helps

Regards,

Ashish Mathur
 
Top