• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

Separate date in Excel Field

I have a field in Excel that has both first name and last name separated by a space. I would like to pull out only the first name into a new column. Is there a formula that will do this?

Here is what the data looks like:
Joe Smith
Randy Roberts
Carl Heinz
Harold Harrison
Mark Grondin
Mark Grondin
2 Solutions
Select the data you want to modify --> Select the Data tab --> select Text to columns --> Select delimited --> Next --> Select "Space" only --> Select Finish

the formula would be

=LEFT(A1,FIND(" ",A1)-1)

Open in new window

and for the last name if first name is in B1

Open in new window

Try This :

  =IFERROR(LEFT(F10,FIND(" ",F10)-1),F10)
Mark GrondinAuthor Commented:
Thank you for your help. =IFERROR(LEFT(F10,FIND(" ",F10)-1),F10) helped me deal with the exceptions, and I ended up using both the codes for first and last names (adding the "IFERROR" function to the lastname funciton suggested). I also returned a blank instead of the original contents of the cell, so my final functions I used are:
first name: =IFERROR(LEFT(L2,FIND(" ",L2)-1),"")
last name: =IFERROR(RIGHT(L2,LEN(L2)-LEN(V2)-1),"")

Wasn't exactly sure how to use the first recommendation, so I can not confirm how it works.

Thanks for your help!
Mark GrondinAuthor Commented:
Requested help to get the points correctly divided...

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now