Experts:
I need some assistance with an **IN BETWEEN TWO CHARACTERS** query.
Below are two (2) sample strings which include variable length last names, first names, and ranks:
Jackson, Tom ~ PO2 (E5)
Smith, Mary Ann ~ PO1 (E6)
- All last names and first names are separated by a comma (,)
- All first names and ranks are separated by a tilde (~)
Now, I figured out how to extract both the last name and rank title. However, I have NOT successfully extracted the variable length first name.
LastName: Left([CHOPS],InStr([CHOPS],", ")-1):
Jackson
Smith
RankTitle: Mid([CHOPS],InStr([CHOPS],"~")+2)
PO2 (E5)
PO1 (E6)
For first name though, I tried the following: FirstName: Mid([CHOPS],InStr([CHOPS],",")+2,Len([CHOPS])-InStr([CHOPS]," ~ ")-1)
I only provided these two examples (Tom, Ann); unfortunately, based on my current data set, the names are something chopped of though. How to I modify the FirstName query so that I get all characters between the "," and "~" (without leading/trailing spaces before/after name?
Please keep in mind that some first names have blank space (e.g., Mary Ann) while others may be hyphenated (Jean-Pierre).
Thank you,
EEH