parse out one excel field

i have an excel column that i need to parse out.  the column contains a person's last name a comma first name and then date of birth...  

eg:

Resident
Smith, John 10-1-1974
Doe, Jane 1-4-1969

i need to parse the above out as:
Filing Name      First Name                 Last Name                 DOB
Smith, John      John                          Smith                         10-01-1974
Doe, Jane        Jane                          Doe                            01-04-1969

thanks for the help!
intsupAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

you can use Text to Columns to do that.

First, use Text to Columns with the comma as the delimiter. This will leave you with two columns, First name and the rest of the data. Select the second column that now has Last name and DOB and do another Text to Columns with the space as the delimiter.

If that does not help, please post a bigger data sample in an Excel file.

cheers, teylyn
0
ry_ashpoolCommented:
=TRIM(LEFT(A2,FIND(",",A2)-1))

Open in new window

Lastname

=TRIM(MID(A2,FIND(",",A2)+1,MIN(IF(ISERROR(FIND(1,A2)),100,FIND(1,A2)),IF(ISERROR(FIND(2,A2)),100,FIND(2,A2)),IF(ISERROR(FIND(3,A2)),100,FIND(3,A2)),IF(ISERROR(FIND(4,A2)),100,FIND(4,A2)),IF(ISERROR(FIND(5,A2)),100,FIND(5,A2)),IF(ISERROR(FIND(6,A2)),100,FIND(6,A2)),IF(ISERROR(FIND(7,A2)),100,FIND(7,A2)),IF(ISERROR(FIND(8,A2)),100,FIND(8,A2)),IF(ISERROR(FIND(9,A2)),100,FIND(9,A2)),IF(ISERROR(FIND(0,A2)),100,FIND(0,A2)))-FIND(",",A2)-1))

Open in new window

Firstname

=RIGHT(A2,LEN(A2)-MIN(IF(ISERROR(FIND(1,A2)),100,FIND(1,A2)),IF(ISERROR(FIND(2,A2)),100,FIND(2,A2)),IF(ISERROR(FIND(3,A2)),100,FIND(3,A2)),IF(ISERROR(FIND(4,A2)),100,FIND(4,A2)),IF(ISERROR(FIND(5,A2)),100,FIND(5,A2)),IF(ISERROR(FIND(6,A2)),100,FIND(6,A2)),IF(ISERROR(FIND(7,A2)),100,FIND(7,A2)),IF(ISERROR(FIND(8,A2)),100,FIND(8,A2)),IF(ISERROR(FIND(9,A2)),100,FIND(9,A2)),IF(ISERROR(FIND(0,A2)),100,FIND(0,A2)))+1)

Open in new window

Date
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TigerManCommented:
Here is a complete solution ... please note that if you the name has more than one first names, it will not function i.e.
Jones, Bob 1-1-2011 will work
Jones, Bob Peter 1-1-2011 will not work parsing-name-date.xls
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
These three formulas, with the source in A1 (a bit shorter than the formulas suggested above).

Last Name =LEFT(A1,FIND(",",A1&",")-1)
First Name =MID(A1,FIND(",",A1&",")+2,LEN(A1)-FIND(",",A1)-12)
DOB =RIGHT(A1,10)

See attached. Works with spaces or hyphens in the last name, spaces or hyphens in the first name.

cheers, teylyn
Book2.xlsx
0
RunriggerCommented:
If you are prepared to implement VB code, you can use something like this;

Public Function ParseData(sStringToParse As String, sDelimeter As String, nElement As Integer) As Variant
Dim sArray() As String
    sArray = Split(sStringToParse, sDelimeter)
    ParseData = Trim(sArray(nElement - 1))
End Function

within the worksheet, the formula would then be as follows;

B1=ParseData(A1,",",1) = Firstname
C1=ParseData(ParseData(A1,",",1)," ",1)) = Surname
D1=ParseData(ParseData(A1,",",1)," ",2)) = DOB

etc etc etc
0
RunriggerCommented:
Sorry, try these instead, I need to return the second element to extract the Surname and DOB

Using the following in cell A1 ---> Jones, Bob 1-1-2011

B1=ParseData(A1,",",1) = Surname
C1=ParseData(ParseData(A1,",",2)," ",1)) = Firstname
D1=ParseData(ParseData(A1,",",2)," ",2)) = DOB

You can use different delimeters, as demonstrated and select different element to return in the formula
0
TigerManCommented:
Runrigger's vba solution is the most elegant.
tevlyn's formula cut the last character off the first name so make it First Name =MID(A1,FIND(",",A1&",")+2,LEN(A1)-FIND(",",A1)-11); the date formula uses 10 characters so has a leading space if the month has only 1 char i.e. 1-9; it also doesn't collect that date data as Date type so it cannot be sorted by date etc.
ry_ashpool's formulae work even with multiple first names (cool), is probably the more robust of all the formulae solutions, but again it doesn't treat the date as Date type
mine returns a date value but doesn't work with multiple first names
have you tested any of the above and ??
0
intsupAuthor Commented:
the department providing me with the data has changed the format so i will be posting a new question but thanks for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.