moving first word in a column to a separate column

  • Thread starter Thread starter Jeff
  • Start date Start date
J

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
 
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),"")
 
If A1 may contain leading or trailing spaces, replace in the formula
all A1 with TRIM(A1)
 
First Word: =LEFT(A1,FIND(" ",TRIM(A1)&" ")-1)
Last Word: =TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99))
Click to expand...
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
 
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)
Click to expand...
Thank you again. Appreciate it.

Jeff
 
Back
Top