I was able to get this solution to work:

Note: Row 1 is column headings so my data (and the related formulas)
start on Row 2

Col. A holds the person's name:
Tom Jones
Tom Jones Jr
Tom Jones Sr.
Tom Mike Jones Sr.
Tom M. Jones Sr.

Col. B is a formula to get the first name (returns all the characters
up to the first space)
=LEFT(A2,FIND(" ",A2))

Col. C is a helper column to determine if there is a middle name:
=IF(ISERROR(MID(A2,LEN(B2)+1,FIND("
",MID(A2,LEN(B2)+1,1024)))),"",MID(A2,LEN(B2)+1,FI ND("
",MID(A2,LEN(B2)+1,1024))))

This function will return the second name (i.e. characters from the
second space to the third space in the string). If there is no 3rd
space, then the person has no middle name and the formula returns ""

Col. D determines whether Col. C actually returned the middle name or
the last name by comparing the value in Col. C to the value in Col. E
(the last name - see below)
=IF(EXACT(C2,TRIM(F2)),"",C2)

Col. E returns the last name less any suffixes. The suffix is
determined in Col. F (see below). This formula returns the last name
in the string less the suffix (if any)
=MID(TRIM(LEFT(A2,LEN(A2)-LEN(F2))),FIND("^^",SUBSTITUTE("
"&TRIM(LEFT(A2,LEN(A2)-LEN(F2)))," ","^^",LEN(" "&TRIM(LEFT(A2,LEN(A2)-
LEN(F2))))-LEN(SUBSTITUTE(" "&TRIM(LEFT(A2,LEN(A2)-LEN(F2))),"
",""))))-1+1,1024)

Col. F - J determine the suffix. Ron's UDF in the previous post may
be a better solution than mine but here's how I did it.
Cells G1:J1 include the suffix I want to look for (e.g. G1 = Jr, H1 =
Sr, I1 = Jr., J1 = Sr.
I put the following formula in cells G2:J2
=IF(ISERROR(FIND(G$1,RIGHT($A2,LEN(G$1)))),"",G$1)

this looks for the suffix as the last n characters of the string, with
n being the length of the suffix typed in G1:J1. If it matches, then
it returns the suffix. If not, it returns blank.

Finally, Col. F returns the concatenation of the values in G2:J2
=G2&H2&I2&J2

For example, if the suffix was Jr. then using the values above, G2 =
"", H2="", I2="Jr.", J2="" and F2 would = ""&""&"Jr."&"" or "Jr."

Finally, in col's K and L, I put the names together as requested in
the original post:
Col K: First / Middle name =B2&D2
Col L: Last / Suffix =TRIM(E2&" "&F2)

If anyone wants to see the excel file, just email me.

Tim


On Aug 14, 9:06 pm, Ron Rosenfeld wrote:
On Tue, 14 Aug 2007 12:09:09 -0700, wrote:
I deal with an Excel file that has many names on it all in 1 cell.
Some of the common combinations are below. Is there any way to
separate the 1 column in to 2 columns bases on table below.

FIRST LAST CONVERT TO FIRST LAST

FIRST MIDDLE LAST CONVERT TO FIRST MIDDLE LAST

FIRST M LAST CONVERT TO FIRST M LAST

FIRST M. LAST CONVERT TO FIRST M. LAST

FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX

FIRST MIDDLE LAST SUFFIX
CONVERT TO FIRST MIDDLE LAST SUFFIX

FIRST LAST SUFFIX CONVERT TO FIRST LAST SUFFIX

The problem I have is that all the names are different lengths.

Thanks for your help.

The hard part is determining the Suffix.

Here's one approach with a UDF.

Suffix is a NAME'd cell containing a pipe delimited list of valid Suffixes.
For example: Jr|Sr|II|III|IV|MD|PHd

<alt-F11 opens the VB Editor. Ensure your project is highlighted in the
project explorer window, then Insert/Module and paste the code below into the
window that opens.

To use this:

A1: Full Name
B1: =TRIM(SUBSTITUTE(TRIM(A1),C1,""))
C1: =ReExtr(TRIM(A1),"\w+(\s("&Suffix&"))?$")

================================================== =======
Option Explicit
Function ReExtr(str As String, sPattern As String) As String
'Requires setting reference to Microsoft VBScript Regular Expressions 5.5

Dim re As RegExp
Dim mc As MatchCollection

Set re = New RegExp
With re
.Global = True
.IgnoreCase = True
.Pattern = sPattern
End With

If re.Test(str) = True Then
Set mc = re.Execute(str)
ReExtr = mc(0)
End If
End Function
=====================================
--ron