Trim and InStr to Parse Field in Query

[Rob S.]

I have a field, [Original], delimited by "/" that contains up to 10 segments (sometimes the field has less, it varies). The length of any given segment may vary also, depending upon the particular row and the particular segment (hence the "/"). If it is of significance, the final segment, does not have a "/" trailer.

I would like to separate [Original], but I can't seem to get beyond the first two segments. I simply used the example shown in Microsoft Article 115915 (http://support.microsoft.com/default.aspx?scid=kb;en-us;115915), and changed the field name, and the delimiter, from " " to "/".

Expr1: Trim(Mid([Names],InStr(1,[Names],"/")+1,InStr(InStr(1,[Names],"/")+1,[Names],"/")-InStr(1,[Names],"/")))
Expr2: Trim(Mid([Names],InStr(2,[Names],"/")+1,InStr(InStr(2,[Names],"/")+1,[Names],"/")-InStr(2,[Names],"/")))

I've tried to substitute increasing values for the 1's and 2's, hoping that they refer to the next segment, but I haven't had any luck yet.

Could anyone provide some code, or explain each part of the above expression?

 

[Guest]

can you post an example of the input and an example of
what you want it to look like when finished.

 

[Rob S.]

Sure! Thanks for looking at it!

Example Input:
/ZNA
/MRS 72/CONS A52F/+REN34V76N2.SD
/CONS A53F/ZNA/SM 52244-6562/6INTRA 7 X/PICO 14 F

Example Output
[Field1] [Field2] [Field3] [Field4]
ZNA
MRS 72 CONS A52F +REN34V76N2.SD
CONS A53F ZNA/SM 52244-6562 6INTRA 7 X PICO 14 F

 

[Rob S.]

I accidentally neglected to parse "ZNA" from "SM 52244-6562" in the last row.

----- Rob S. wrote: -----

Sure! Thanks for looking at it!

Example Input:
/ZNA
/MRS 72/CONS A52F/+REN34V76N2.SD
/CONS A53F/ZNA/SM 52244-6562/6INTRA 7 X/PICO 14 F

Example Output
[Field1] [Field2] [Field3] [Field4]
ZNA
MRS 72 CONS A52F +REN34V76N2.SD
CONS A53F ZNA/SM 52244-6562 6INTRA 7 X PICO 14 F