Parse Comma Delimited field

[asmithusa]

Field is: LastN Suffix (e.g. Jr.), FirstN MiddleN (note comma between Suffix & FirstN

The field's data may have only LastN and FirstN or any combination of the above (2 up to 4 words).

e.g. Smith, Allen (2 names) or Smith Jr., Allen (3 names) or Smth, Allen Sterling (3 names) or Smith jr., Allen Sterling (4 names)

The result needed for each field is -

FirstN MiddleN (if any) LastN and Suffix (if any) in one field with out comma

I have seen postings about saving a copy as global. Is this a file or field? And using the Position function. All in all I am totally confused. Any help would be appreciated.

[danjacoby]

Assuming there's no comma between the LastN and the Suffix (as there theoretically should be), you can use the "Position" command to find the first word after the comma. Sorry, too tired right now to do the whole thing for you, but it's a start.

[Fitch]

I'll be the Professor of Logic for today...

Last Name =

Left(Name, Position(Name, ",", 1, 1) - 1)

Here the Position() function tells us what character number the comma is, and since we don't want the comma, we subtract one. The Left() function tells us to get that many characters.

First Name =

Right(Name, Length(Name) - Position(Name, ",", 1, 1))

Here we need to sort of flip the first function around, so we get the Length of the Name and subtract the function we used in the first step.

so the Full Name = Right(Name, Length(Name) - Position(Name, ",", 1, 1)) & " " & Left(Name, Position(Name, ",", 1, 1) - 1)

I know it's a bit of a mind-bender. Have some coffee and read the online help, it explains it better than I could.

[asmithusa]

Thanks to both of you for the help. Dan got me started in the correct direction and Fitch gave me the "nuts and bolts". It is working beautifully.

[danjacoby]

Not exactly Tinker to Evers to Chance, but then I was never a Cubs fan anyway.