PC Review


Reply
Thread Tools Rate Thread

moving first word in a column to a separate column

 
 
Jeff@nospam.invalid
Guest
Posts: n/a
 
      13th May 2011
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
 
Reply With Quote
 
 
 
 
Gord Dibben
Guest
Posts: n/a
 
      14th May 2011
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, "(E-Mail Removed)" <(E-Mail Removed)>
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

 
Reply With Quote
 
 
 
 
Charabeuh
Guest
Posts: n/a
 
      14th May 2011
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



 
Reply With Quote
 
Charabeuh
Guest
Posts: n/a
 
      14th May 2011
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),"")
>
>
>
>
>



 
Reply With Quote
 
Jeff@nospam.invalid
Guest
Posts: n/a
 
      14th May 2011
On 5/13/11 9:09 PM, Ron Rosenfeld wrote:
> On Fri, 13 May 2011 17:41:49 -0400, "(E-Mail Removed)"<(E-Mail Removed)> 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.
 
Reply With Quote
 
Jeff@nospam.invalid
Guest
Posts: n/a
 
      14th May 2011
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.
 
Reply With Quote
 
Jeff@nospam.invalid
Guest
Posts: n/a
 
      14th May 2011
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, "(E-Mail Removed)"<(E-Mail Removed)>
> 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


 
Reply With Quote
 
Jeff@nospam.invalid
Guest
Posts: n/a
 
      14th May 2011
On 5/13/11 9:09 PM, Ron Rosenfeld wrote:
> On Fri, 13 May 2011 17:41:49 -0400, "(E-Mail Removed)"<(E-Mail Removed)> 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

 
Reply With Quote
 
Jeff@nospam.invalid
Guest
Posts: n/a
 
      14th May 2011
On 5/14/11 9:18 AM, Ron Rosenfeld wrote:
> On Sat, 14 May 2011 08:46:32 -0400, "(E-Mail Removed)"<(E-Mail Removed)> wrote:
>
>> On 5/13/11 9:09 PM, Ron Rosenfeld wrote:
>>> On Fri, 13 May 2011 17:41:49 -0400, "(E-Mail Removed)"<(E-Mail Removed)> 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
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: creating last name first, first name last from a column of first name last name data Rick Rothstein Microsoft Excel Programming 0 27th Jul 2009 07:25 AM
Apartment Moving, Office Moving, Commercial Moving, Moving Companiesin your area. linkswanted Microsoft C# .NET 0 6th Jan 2008 05:46 AM
Free Moving Estimate, Local Movers, Long Distance Moving, PackingSupplies, Storage Rental, Home Moving, Apartment Moving, Office Moving,Commercial Moving linkswanted Microsoft ASP .NET 0 6th Jan 2008 05:45 AM
Separate first and second name in one cell into separate cells. =?Utf-8?B?RHdpZ2h0IGluIEdlb3JnaWE=?= Microsoft Excel Misc 3 25th Jan 2006 10:09 PM
RE: Separate first and second name in one cell into separate cells. =?Utf-8?B?R2FyeSdzIFN0dWRlbnQ=?= Microsoft Excel Misc 0 27th Apr 2005 11:11 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 07:33 PM.