Separating names

Good day. I am using Excel 2002 on Windows XP Pro. I have a worksheet
contining names exported from a DBF database. I need to import this list of
names into a relational database that looks for First Name, Middle Name, Last
Name and Suffix. There are 900 names in this list.

I am currently separating the names as best I can manually. I know there
must be an easier way.

The names are in many formats. For example:

Test Tester
Test T. Tester
Test Tester Sr
Test T. Tester Jr

Is there a way to separate all of this into separate fields as mentioned. I
am familiar with Left, Right, Mid functions and using Find within these
functions, but I have not been able to find the magic solution. I would
appreciate any help.

Thanks, Danno...

With many (and perhaps some yet unkown) variations, I probably wouldn't look
for a silver bullet. Rather, create a helper column the looks for particular
formats and returns TRUE for a match and FALSE otherwise. Ex:
=NOT(ISERROR(FIND(". ",A1)))
would pick up the instances of a middle initial.
Filter on TRUE and handle that particular case. Then handle the next case.
My two cents. --Bruce

"Danno" wrote:

> Good day. I am using Excel 2002 on Windows XP Pro. I have a worksheet
> contining names exported from a DBF database. I need to import this list of
> names into a relational database that looks for First Name, Middle Name, Last
> Name and Suffix. There are 900 names in this list.
>
> I am currently separating the names as best I can manually. I know there
> must be an easier way.
>
> The names are in many formats. For example:
>
> Test Tester
> Test T. Tester
> Test Tester Sr
> Test T. Tester Jr
>
> Is there a way to separate all of this into separate fields as mentioned. I
> am familiar with Left, Right, Mid functions and using Find within these
> functions, but I have not been able to find the magic solution. I would
> appreciate any help.
>
> Thanks, Danno...

Data>Text To Columns

--

HTH

RP
(remove nothere from the email address if mailing direct)


"bpeltzer" <bpeltzer@discussions.microsoft.com> wrote in message
news:C777C75E-0F81-4B9E-829B-784BDFE21EE1@microsoft.com...
> With many (and perhaps some yet unkown) variations, I probably wouldn't
look
> for a silver bullet. Rather, create a helper column the looks for
particular
> formats and returns TRUE for a match and FALSE otherwise. Ex:
> =NOT(ISERROR(FIND(". ",A1)))
> would pick up the instances of a middle initial.
> Filter on TRUE and handle that particular case. Then handle the next
case.
> My two cents. --Bruce
>
> "Danno" wrote:
>
> > Good day. I am using Excel 2002 on Windows XP Pro. I have a worksheet
> > contining names exported from a DBF database. I need to import this
list of
> > names into a relational database that looks for First Name, Middle Name,
Last
> > Name and Suffix. There are 900 names in this list.
> >
> > I am currently separating the names as best I can manually. I know
there
> > must be an easier way.
> >
> > The names are in many formats. For example:
> >
> > Test Tester
> > Test T. Tester
> > Test Tester Sr
> > Test T. Tester Jr
> >
> > Is there a way to separate all of this into separate fields as
mentioned. I
> > am familiar with Left, Right, Mid functions and using Find within these
> > functions, but I have not been able to find the magic solution. I would
> > appreciate any help.
> >
> > Thanks, Danno...

Danno,
In his book "Excel 2003 Formulas" John Walkenbach dedicates 2 pages to
this topic. Although I have done this, in practice I just use Text to
Columns with a space as a seperator.

Lenny

Provided that your formats are consistent with those in your example,
and assuming that Column A contains your data, try the following...

Helper column...

B1, copied down:

=IF(OR(ISNUMBER(SEARCH({"Sr","Jr"},A1))),TRIM(LEFT(TRIM(A1),SEARCH("^^",S
UBSTITUTE(TRIM(A1)," ","^^",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),"
",""))))-1)),TRIM(A1))

First name...

C1, copied down:

=LEFT(B1,FIND(" ",B1)-1)

Middle initial...

D1, copied down:

=IF(LEN(B1)-LEN(SUBSTITUTE(B1," ",""))>1,MID(B1,FIND(" ",B1)+1,(FIND("
",B1,FIND(" ",B1)+1)-1)-FIND(" ",B1)),"")

Last name...

E1, copied down:

=MID(B1,FIND(" ",B1,IF(LEN(B1)-LEN(SUBSTITUTE(B1," ",""))>1,FIND("
",B1)+1,1))+1,255)

Suffix...

F1, copied down:

=IF(ISNUMBER(SEARCH("Sr",A1)),"Sr",IF(ISNUMBER(SEARCH("Jr",A1)),"Jr",""))

To convert these to values...

1) Select Columns C through F

2) Edit > Copy > Edit > Paste Special > Values > Ok

Hope this helps!

In article <ADD80942-4724-4E55-969B-8F7D577993F1@microsoft.com>,
"Danno" <Danno@discussions.microsoft.com> wrote:

> Good day. I am using Excel 2002 on Windows XP Pro. I have a worksheet
> contining names exported from a DBF database. I need to import this list of
> names into a relational database that looks for First Name, Middle Name, Last
> Name and Suffix. There are 900 names in this list.
>
> I am currently separating the names as best I can manually. I know there
> must be an easier way.
>
> The names are in many formats. For example:
>
> Test Tester
> Test T. Tester
> Test Tester Sr
> Test T. Tester Jr
>
> Is there a way to separate all of this into separate fields as mentioned. I
> am familiar with Left, Right, Mid functions and using Find within these
> functions, but I have not been able to find the magic solution. I would
> appreciate any help.
>
> Thanks, Danno...

Hi,

This is not a perfect solution but will take care of most of your scenarios

Assuming the name is in cell A32, enter the following array formula
(Ctrl+Shift+Enter) in cell B32 to get the first name

MID(A32,MATCH(TRUE,EXACT(MID(A32,ROW($1:$40),1),PROPER(MID(A32,ROW($1:$40),1))),0),MATCH(TRUE,EXACT(MID(A32,ROW($2:$40),1),PROPER(MID(A32,ROW($2:$40),1))),0))

Middle Name

IF(ISERROR(MID(A32,SEARCH(" ",A32,1)+1,SEARCH("
",A32,MATCH(32,CODE(MID(A32,ROW($1:$40),1)),0)+1)-SEARCH(" ",A32,1))),"
",MID(A32,SEARCH(" ",A32,1)+1,SEARCH("
",A32,MATCH(32,CODE(MID(A32,ROW($1:$40),1)),0)+1)-SEARCH(" ",A32,1)))

Last name

IF(ISERROR(MID(A32,SEARCH("
",A32,MATCH(32,CODE(MID(A32,ROW($1:$40),1)),0)+1)+1,255)),MID(A32,MATCH(TRUE,EXACT(MID(A32,ROW($2:$40),1),PROPER(MID(A32,ROW($2:$40),1))),0)+2,255),MID(A32,SEARCH(" ",A32,MATCH(32,CODE(MID(A32,ROW($1:$40),1)),0)+1)+1,255))

Regards,

Ashish Mathur

"Danno" wrote:

> Good day. I am using Excel 2002 on Windows XP Pro. I have a worksheet
> contining names exported from a DBF database. I need to import this list of
> names into a relational database that looks for First Name, Middle Name, Last
> Name and Suffix. There are 900 names in this list.
>
> I am currently separating the names as best I can manually. I know there
> must be an easier way.
>
> The names are in many formats. For example:
>
> Test Tester
> Test T. Tester
> Test Tester Sr
> Test T. Tester Jr
>
> Is there a way to separate all of this into separate fields as mentioned. I
> am familiar with Left, Right, Mid functions and using Find within these
> functions, but I have not been able to find the magic solution. I would
> appreciate any help.
>
> Thanks, Danno...