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


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!
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:

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

Open in new window



Open in new window



Open in new window


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
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
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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)

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

cheers, teylyn
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
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
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 ??
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!
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.