moving first word in a column to a separate column

Discussion in 'Microsoft Excel Discussion' started by Jeff@nospam.invalid, May 13, 2011.

  1. Guest

    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
     
    , May 13, 2011
    #1
    1. Advertisements

  2. Gord Dibben Guest

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

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


    Gord Dibben MS Excel MVP

    On Fri, 13 May 2011 17:41:49 -0400, "" <>
    wrote:

    >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, May 14, 2011
    #2
    1. Advertisements

  3. Charabeuh Guest

    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),"")










    > 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
     
    Charabeuh, May 14, 2011
    #3
  4. Charabeuh Guest

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





    > 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, May 14, 2011
    #4
  5. Guest

    On 5/13/11 9:09 PM, Ron Rosenfeld wrote:
    > On Fri, 13 May 2011 17:41:49 -0400, ""<> wrote:
    >
    >> 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

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

    Thank you very much.
     
    , May 14, 2011
    #5
  6. Guest

    On 5/13/11 8:29 PM, Charabeuh wrote:
    > If A1 may contain leading or trailing spaces, replace in the formula all
    > A1 with TRIM(A1)
    >
    >
    >
    >
    >
    >> 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),"")
    >>

    Thank you very much.
     
    , May 14, 2011
    #6
  7. Guest

    Thanks.

    On 5/13/11 7:47 PM, Gord Dibben wrote:
    > See Chip's site for extracting first, middle and last names.
    >
    > http://www.cpearson.com/excel/FirstLast.htm
    >
    >
    > Gord Dibben MS Excel MVP
    >
    > On Fri, 13 May 2011 17:41:49 -0400, ""<>
    > wrote:
    >
    >> 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
     
    , May 14, 2011
    #7
  8. Guest

    On 5/13/11 9:09 PM, Ron Rosenfeld wrote:
    > On Fri, 13 May 2011 17:41:49 -0400, ""<> wrote:
    >
    >> 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

    >
    > 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
     
    , May 14, 2011
    #8
  9. Guest

    On 5/14/11 9:18 AM, Ron Rosenfeld wrote:
    > On Sat, 14 May 2011 08:46:32 -0400, ""<> wrote:
    >
    >> On 5/13/11 9:09 PM, Ron Rosenfeld wrote:
    >>> On Fri, 13 May 2011 17:41:49 -0400, ""<> wrote:
    >>>
    >>>> 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
    >>>
    >>> 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.

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

    Thank you again. Appreciate it.

    Jeff
     
    , May 14, 2011
    #9
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Piotr

    How to separate 2 words into separate kolumns

    Piotr, Jan 27, 2005, in forum: Microsoft Excel Discussion
    Replies:
    2
    Views:
    194
  2. J.W. Aldridge

    Separate at first number

    J.W. Aldridge, May 5, 2008, in forum: Microsoft Excel Discussion
    Replies:
    4
    Views:
    136
    Rick Rothstein \(MVP - VB\)
    May 5, 2008
  3. shabutt

    Moving in a column by first letter of data in cells

    shabutt, Dec 1, 2008, in forum: Microsoft Excel Discussion
    Replies:
    8
    Views:
    160
    Don Guillett
    Dec 5, 2008
  4. Shane R. Pouch

    Separating First Name(s) and Last Name into Separate Columns

    Shane R. Pouch, Dec 16, 2008, in forum: Microsoft Excel Discussion
    Replies:
    3
    Views:
    299
    Ron Rosenfeld
    Dec 17, 2008
  5. dranon

    Separate worksheet in separate workbook as macro

    dranon, May 2, 2009, in forum: Microsoft Excel Discussion
    Replies:
    5
    Views:
    273
    Dave Peterson
    May 3, 2009
Loading...

Share This Page