Parsing Data

[dferreira]

I have a task to parse data in a query and separate it into First and Last Name. The data looks like this: ARRIGHI ROBIN where Robin is first name and Arrighi is last name. I have 29,000 records like this.

Do you have any suggestions?

Donna

 

[MSAccessRookie]

I have a task to parse data in a query and separate it into First and Last Name. The data looks like this: ARRIGHI ROBIN where Robin is first name and Arrighi is last name. I have 29,000 records like this.

Do you have any suggestions?

Donna

The simplest solution that will work for the majority of cases (where there is exactly one space), involves the use of the Left(), Right(), and Instr() Functions. The LastName is defined as everything before the space, and the FirstName is defined as everything after the space. Something like the following will work:

LastName: Left(YourData, Instr(1, YourData, " ")-1)
FirstName: Right(YourData, Instr(1, YourData, " ")+1)

Note that for Data that has more than one space, this will probably not work the way that you want. You will need to have additional logic to handle cases where there is not exactly one space.