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

"Caden" wrote:


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
------------------------------------------------------------------------
Caden's Profile: http://www.excelforum.com/member.php...o&userid=27252
View this thread: http://www.excelforum.com/showthread...hreadid=467583