Sorting

[Caden]

Hey all.

I have a column with users first name and last name inside the same
single column

What I want to do is split the data, into 2 columns and I can do this.

Problem is, not all the data is the same, for instance

John Doe
Dr John Doe
John Doe III

etc...

Therefore, I am wondering if there is a way to sort the data so the
column will show the fields with FirstName Lastname first and then sort
the exceptions in no parciular order.

Thanks
Caden

 

[Caden]

I'll try to be more clear

What I want, is for excel to sort based on 2 words...so, if the fiel
has 2 words, I want that at the top, if the field has more then 2 word
I want that at the bottom...in no particular order, just simply by
words

Edit: I can go into the options and set a custom list, all i'm lookin
for is the formula to tell excel sort by A B, A B C, A B C D
etc

And the A and B are variables, they can be anythin

 

[Guest]

Hi,

There may be more elegant approaches. But the following formula could do it.

First create a list of prefixes that are likely to occur in the names in a
separate helper column. Here, I made such a list in F2:F11 (Mr, Mrs, Miss,
Ms, Dr, Mr., Mrs., Miss., Ms., Dr.). I am assuming that the names are in
Column A, starting at A2. The formula goes in B2, and then fill-down the
column.

=IF(ISNUMBER(MATCH(LEFT(A2,FIND(" ",A2)-1),$F$2:$F$11,0)),MID(A2,FIND("
",A2)+1,100)&" "&LEFT(A2,FIND(" ",A2)-1),A2)

For the formula to work, there should be space between the prefix and the
first name even when the prefix ends with a period.

Regards,
B. R. Ramachandran