How to pull a partial string from the middle of a field?

[rich575]

The string has been imported into one field from a csv file. A sample of the
string is:

First Name: Mike, Last Name: Harris, Address: 65 Main St.
First Name: Richard, Last Name: Smith, Address: 107 Washington Ave.

Goals:
1. Pull out each type of information into a separate field (First Name
field, Last Name field, etc.)
2. Eliminate the field labels from the data, (i.e. First Name:, Last Name:,
etc.)

Some of the difficulties are that the fields lengths each have a different
number of characters, so the Left() or Right() function can't be used.

Is there a way to pull a partial string out, like just the Last Name field
label and field value, or ideally just the field value?

Thanks,
rich575(removethis)@aol.com

 

[Ofer]

You can try and check the split function to split the string into parts

Split(FieldName," ")(0) will return First name
Split(FieldName," ")(1) will return last name

 

[Allen Browne]

Presumably you already have the target fields set up, so you will use an
Update query to populate them, and then delete this combined field once you
have verified everything is okay.

In the Update query, use Instr() to locate where the field name occurs, and
Mid() to parse the data from the field. For example:
Instr(", First Name: Mike, Last Name: Harris, Address: 65 Main St.",
"Last Name:")
yields 17, because in this example "Last Name:" begins at the 17th
character.

The surname therefore starts 12 characters later, at:
Instr("First Name: Mike, Last Name: Harris, Address: 65 Main St.", "Last
Name:") + 12

Using similar logic, you can parse where the ", Address:" characters are
found.

Now use these values in the Mid() function to pull those parts out of the
string, and populate your other fields with them in your Update query.
Trim() the results to get rid of leading/trailing spaces.

 

[Douglas J. Steele]

No, it won't.

Split(FieldName, " ")(0) will return the word "First", while
Split(FieldName, " ")(1) will return the word "Last" given the data posted
below.

You can get First Name: Mike if you use Split(FieldName, ",")(0) instead,
and Last Name: Harris if you use Split(FieldName, ",")(1). Once you've got
that, you can use the Split function again (with ":" as the delimiter) to
get only the value of interest.

In other words, Split(Split(FieldName, ",")(0), ":")(1) will give you Mike,
while Split(Split(FieldName, ",")(1), ":")(1) will give you Harris.

 

[Ofer]

Thank you for paying more attention then I do.

No, it won't.

Split(FieldName, " ")(0) will return the word "First", while
Split(FieldName, " ")(1) will return the word "Last" given the data posted
below.

You can get First Name: Mike if you use Split(FieldName, ",")(0) instead,
and Last Name: Harris if you use Split(FieldName, ",")(1). Once you've got
that, you can use the Split function again (with ":" as the delimiter) to
get only the value of interest.

In other words, Split(Split(FieldName, ",")(0), ":")(1) will give you Mike,
while Split(Split(FieldName, ",")(1), ":")(1) will give you Harris.