Sorting

  • Thread starter Thread starter Caden
  • Start date Start date
C

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
 
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
 
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
 
Back
Top