moving first word in a column to a separate column

[Jeff]

In an Excel 2007 with 26,000 rows I have a column that contains names
with 2 or 3 words.
Example:
John Andrew McPherson
Phillip something something Anderson

I would like to split this into 2 columns, one for the first name and
the other for the last word (which may be the second or 3rd or even 4th).

What would be a way to automatically move:

John to a Given Name column and McPherson to the surname column, and
Phillip to the Given Name column and Anderson to the surname column.

Thanks.

Jeff

 

[Gord Dibben]

See Chip's site for extracting first, middle and last names.

http://www.cpearson.com/excel/FirstLast.htm


Gord Dibben MS Excel MVP

 

[Charabeuh]

Hello,

You could try these formlulas:

First word:
=IF(ISNUMBER(FIND(" ",A1)),LEFT(A1,FIND(" ",A1)-1),A1)

Last Word:
=IF(ISNUMBER(FIND(" ",A1)),MID(SUBSTITUTE(A1,"
","µ",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),FIND("µ",SUBSTITUTE(A1,"
","µ",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,99),"")

 

[Charabeuh]

If A1 may contain leading or trailing spaces, replace in the formula
all A1 with TRIM(A1)

 

[Jeff]

First Word: =LEFT(A1,FIND(" ",TRIM(A1)&" ")-1)
Last Word: =TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99))

Thank you very much.

 

[Jeff]

If A1 may contain leading or trailing spaces, replace in the formula all
A1 with TRIM(A1)

Thank you very much.

 

[Jeff]

Thanks.

See Chip's site for extracting first, middle and last names.

http://www.cpearson.com/excel/FirstLast.htm


Gord Dibben MS Excel MVP

 

[Jeff]

First Word: =LEFT(A1,FIND(" ",TRIM(A1)&" ")-1)
Last Word: =TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99))

Formulas worked superbly. Thank you.

Here's a more difficult one (if I may). I may have to do it manually.

column contains:
Claire ep. Yaacov(Jacky) Toledano
Eliette bat Armand
Evelyne epouse Albert Cohen
Abraham ben Yaacov

First name is solved using your formula.

Need formula that would do the following:
-if bat or ben exists, place what follows in column D (for father's name)
-if ep. or epoux, or epouse found. place what follows in column E (for
spouse's name)

Is that feasible?

Thanks.

Jeff

 

[Ron Rosenfeld]

Formulas worked superbly. Thank you.

Here's a more difficult one (if I may). I may have to do it manually.

column contains:
Claire ep. Yaacov(Jacky) Toledano
Eliette bat Armand
Evelyne epouse Albert Cohen
Abraham ben Yaacov

First name is solved using your formula.

And the last word is not???

Need formula that would do the following:
-if bat or ben exists, place what follows in column D (for father's name)
-if ep. or epoux, or epouse found. place what follows in column E (for
spouse's name)

Is that feasible?

Thanks.

Jeff

Glad to help.

Try this:

D1: =MID(TRIM(A1),MIN(SEARCH({" bat "," ben "},TRIM(A1)&" bat ben "))+5,99)
E1: =MID(TRIM(A1),MIN(SEARCH({" ep. "," epouse "},TRIM(A1)&" ep. epouse "))+5 + ISNUMBER(SEARCH(" epouse ",A1))*3,99)

 

[Jeff]

And the last word is not???




Glad to help.

Try this:

D1: =MID(TRIM(A1),MIN(SEARCH({" bat "," ben "},TRIM(A1)&" bat ben "))+5,99)
E1: =MID(TRIM(A1),MIN(SEARCH({" ep. "," epouse "},TRIM(A1)&" ep. epouse "))+5 + ISNUMBER(SEARCH(" epouse ",A1))*3,99)

Thank you again. Appreciate it.

Jeff

 

[Ron Rosenfeld]

Thank you again. Appreciate it.

Jeff

Glad to help.

Thanks for the feedback.