separate field into parts (1 Viewer)

[deekras]

i have a name field that includes a first middle (or initial) last and sometimes a rank (jr, sr. III). I want to separate the field into 4 fields. My problem is that only some have rank, so i am having a problem with the last name.

first
middle
last
rank

how do i do that?

 

[llkhoutx]

Use the Instr function.

First search for a ","; to tthe right is a space and your forth field. If no comma, no fourth field.

Then search for last space (before the comma) and the 3rd field is to the right.

Then search from the front for spaces, etc.

Unpacking names is frought with problems: what if no middle name, what if first name is unknown, middle initial may be used instead of middle name, rank may not be separated by a comma to name a few.

 

[deekras]

you are right, the rank is nor always separated by a comma. but the rank is always either Jr or Jr. or Sr. or Sr or III or II. Can I do anything with that?