More help please with contact list....

Thanks to those of you that helped me with my first question. I have two more.

I needed to know how to make my "Jane Doe" in one cell look like "Doe, Jane." Accomplished...thank you. Now I need to know how to make my "John & Jane Doe" look like "Doe, John & Jane." The formula I received made it look like "Jane Doe, & John."

Next question....once I have all of those, how do I paste them in the column I need without getting "Ref.?"

Warned you about the structure of your data ...
Can you give us one example of each of your possibilities ...
in order to come up with a comprehensive answer ...?

Carim

Originally Posted by ckluge

Thanks to those of you that helped me with my first question. I have two more.

I needed to know how to make my "Jane Doe" in one cell look like "Doe, Jane." Accomplished...thank you. Now I need to know how to make my "John & Jane Doe" look like "Doe, John & Jane." The formula I received made it look like "Jane Doe, & John."

Next question....once I have all of those, how do I paste them in the column I need without getting "Ref.?"

this is going to be a real pain in the butt because you're going to have a really hard time knowing where to seperate.
For example John & Jane Doe seperates first names from last names at the third space. But some lastnames have spaces in them, so a name like John Van LastName seperates at the second space and then some names like John Doe seperates at the first space.
I have a feeling you're going to have to manually go through your list and correct the exceptions to your original rule.
If all your contacts with two names have a & in them we can use that to help, but it'll be almost impossible to account for all possiblities

Hi Carim, below are the possibilities:

John A. & Jane A. Doe
John & Jane Doe
John A. Jr. & Jane Doe

Thanks for your help. =)

Originally Posted by ckluge

Hi Carim, below are the possibilities:

John A. & Jane A. Doe
John & Jane Doe
John A. Jr. & Jane Doe

Thanks for your help. =)

This is useful, but you never indicated whether or not what MDubbelboer suggested was possible, such as the following:

John A. & Jane A. Van Doe, where Van Doe is the last name.

Originally Posted by Maistrye

This is useful, but you never indicated whether or not what MDubbelboer suggested was possible, such as the following:

John A. & Jane A. Van Doe, where Van Doe is the last name.

assuming last names never have a space in them we can return the string to the right of the last space as the last name, and follow with the first name with this monstrosity courtesy of scott:
=RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))&", "&LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

No names are separate currently but it is a possibility in the future. This last sum is not working for me.

Nevermind, it works great. Thanks for all your help everyone!

Originally Posted by ckluge

Nevermind, it works great. Thanks for all your help everyone!

Hi ckluge,

Never give up, there is always an answer

Your examples in A1:A3

John A. & Jane A. Doe
John & Jane Doe
John A. Jr. & Jane Doe

In B1 put this as an array formula, copy it, but enter it as Ctrl Shift Enter

=RIGHT(A1,LEN(A1)-MAX(ROW(INDIRECT("1:"&LEN(A1))) *(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")))

Then in C1, put this, but not as an array

=B1&", "&LEFT(A1,LEN(A1)-LEN(B1))

Result

Doe, John A. & Jane A.
Doe, John & Jane
Doe, John A. Jr. & Jane

oldchippy

Oldchippy,

You are a Saint ... !!!

Cheers
Carim