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

[Will61768]

I want to take a list that has in a column Firstname Lastname and what I 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 that was clear.


Thanks for any suggestions you can offer.

[swatsp0p]

You need to give us the specific layout of the text in the cell and a 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 formula will return "Smith, John":

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

If not, more info, please.

Good Luck

[Bob Phillips]

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

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

--
HTH

Bob Phillips

"Will61768" <[email protected]> wrote
in message news:[email protected]...
>
> I want to take a list that has in a column Firstname Lastname and what I
> 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 that
> was clear.
>
>
> Thanks for any suggestions you can offer.
>
>
> --
> Will61768
>
>
> ------------------------------------------------------------------------
> Will61768's Profile:
http://www.excelforum.com/member.php...o&userid=27922
> View this thread: http://www.excelforum.com/showthread...hreadid=474181
>

[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


"Will61768" wrote:

>
> I want to take a list that has in a column Firstname Lastname and what I
> 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 that
> was clear.
>
>
> Thanks for any suggestions you can offer.
>
>
> --
> Will61768
>
>
> ------------------------------------------------------------------------
> Will61768's Profile: http://www.excelforum.com/member.php...o&userid=27922
> View this thread: http://www.excelforum.com/showthread...hreadid=474181
>
>