asked on

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!

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

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

ASKER CERTIFIED SOLUTION

ry_ashpool

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TigerMan

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

SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Runrigger

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

SOLUTION

Runrigger

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

TigerMan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jose C

ASKER

the department providing me with the data has changed the format so i will be posting a new question but thanks for your help!