excel-how do i copy a part of a column 3

[tempo1]

Hi everyone,
I have 2 columns in my Excel sheet. First column is empty, the secon contains 2 words: first name, last name.
I want to remove the first word (first name) out of the second column into the first. There is space separating between the 2 words in column 2 but length of words varies from row to row.
I could do it manually but there is 5000 rows...
Can anyone help me with that please ?
Thanks a lot.

 

[SkipVought]

Hi,

Check out Data/Text to columns, DELIMITED by SPACE

Skip,
[sub]
[red][/red]
[/sub]

 

[Hasit]

I pinched this from CPearson:

Assuming first column is blank, second colum has surname firstname - e.g.

Bush George

then this formula will work:

=LEFT(B1,IF(ISERROR(FIND(" ",B1,1)),LEN(B1),FIND(" ",B1,1)-1))

 

[yogia]

Hi tempo1:

To split the FullName into FirstName and Lastname using Data|Text_to_column as suggested by Skip is the way to go.

If you need a formula based approach, following is one way ... I have assumed that you have the FullNames in cells C2:C7, and I have then extracted the FirstName in column A, and the LastName in column B ...

[tt]
  A               B               C
----------     --------         ------------
1|FirstName	LastName	     FullName
2|Bob	      Smith	        Bob Smith
3|Ken	      Tever	        Ken Tever
4|Dave	     Dotson	       Dave Dotson
5|Matt	     Lincoln	      Matt Lincoln
6|Paul	     Carthew	      Paul Carthew
7|Joel	     Yono	         Joel Yono
[/tt]

formula in cell A2 is ... =LEFT(C2,FIND(" ",C2)-1)
and
formula in cell B2 is ... =MID(C2,FIND(" ",C2)+1,255)

the formulas in cells A2 and B2 are then copied down.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[tempo1]

Hi skipvaught,hasit,vogia
I'm vrey gratefull to you and you solved my problem+taught me few new things about excel.

 

[PaulNeubauer]

I do hope that the previous answers did solve your problem, but I think it would be wise to inject a note of caution. My comment isn't really about Excel, but make sure that you check for names with internal spaces, e.g., first names like "Mary Ellen" or last names like "Lloyd Webber", "Van Kamp" (or "van Kamp"). Non-stereotypical names are surprisingly common. Internal spaces, varying capitalization, no "middle" name, multiple middle names/initials ("George H. W. Bush"), use of initial + name rather than name + initial, etc. can make some common and "obvious" assumptions unsafe.

If you're lucky, none of the names in your spreadsheet will be like that, but with 5000 of them, I would not be at all surprised to find at least a few cases where simply looking for spaces could parse the names incorrectly. You are probably a little better off than you might be if you have "only" first-name and last-name and always in that order. If all you need to parse out is the first-name there will probably be fewer cases of "multi-word" first names than you might likely have to deal with in a general case where you would have to deal with parsing full names (not forgetting things like "Jr." or "III" and the like). It also helps that you have the finite task of parsing only one list of names, one time. Be grateful for small blessings.

So, at the very minimum, I would advise scanning the result to see if you need to fix anything. Scanning 5000 rows and fixing a few is a drag, but at least it's a lot better than going through and separating all the names manually.

Best Regards,
Paul