I have fielded data like this:

[u]OriginalName[/u]
Miller,John A
Jones,Sue
San Luis,Jose Gabriel
Luis-Gomez,Pepe

I want to parse this, giving special treatment to the right of the comma. I only need the first word in the first name. So we are going to be doing some Min and Instr to get where we need to go. The problem I had is that some of the Last Names have a space and some of the First Names do not have a space.

Here is the solution:

iif(InStr((InStr([OriginalName],”,”)+1),[OriginalName],” “)>1,Mid ( [OriginalName], InStr([OriginalName],”,”)+1, InStr((InStr([OriginalName],”,”)+1),[OriginalName],” “) –  InStr([OriginalName],”,”)),Mid ( [OriginalName], InStr([OriginalName],”,”)+1, Len([originalname]) –  InStr([OriginalName],”,”)))

Breaking it down…

First we look for a space after the comma

InStr((InStr([OriginalName],”,”)+1),[OriginalName],” “)>1

If we find a space we grab everything from the comma to the space

Mid( [OriginalName], InStr([OriginalName],”,”)+1,InStr((InStr([OriginalName],”,”)+1),[OriginalName],” “) –  InStr([OriginalName],”,”))

If no space after the comma we grab from the comma to the end of the cell

Mid ( [OriginalName], InStr([OriginalName],”,”)+1, Len([OriginalName]) –  InStr([OriginalName],”,”))

Let’s take a look at the blue and the orange. Notice that they are the same except for the part after the second comma. This is the “number of characters” variable.

Mid ( text, start_position, number_of_characters )

So the text and start position are the same for both blue and orange. It is only the “number of characters” variable which is different.

In the blue our number of characters is the Field Length minus the position of the comma.

Len([OriginalName]) – InStr([OriginalName],”,”)

The orange one is harder because here we need Position of First Space After Comma minus the position of the comma.

InStr((InStr([OriginalName],”,”)+1),[OriginalName],” “) –  InStr([OriginalName],”,”)

So far, every time we have used InStr we have started at position zero. The start position is optional and we have not used it.

Instr ( [start], string_being_searched, string2, [compare] )

However, in this orange bit we need to use the start position variable and the value we want to use is equal to the position of the comma plus 1.