Separating names

[Guest]

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...

 

[Guest]

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

 

[Bob Phillips]

Data>Text To Columns

--

HTH

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

 

[Lenny]

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

 

[Domenic]

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!

 

[Guest]

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