parse data

[DD]

i am trying to get the 18 out of this description field and store it in a
new field. its a pretty consistent string but the number is not always two
digits. all i can think of is parse whats after the 3rd comma and the
/(slash). in this case 18. anyone have an idea on this.

thanks for the help
ACM,GRN BEAN,F.S.,18/16OZP,01

 

[Ken Snell \(MVP\)]

This is one way, assuming that the target number is one or two digits long
only:

Replace(Mid(FieldName,
InStr(InStr(InStr(1,FieldName,",")+1,FieldName,",")+1,FieldName,",")+1,2),"/","",1,1,1)

 

[DD]

Wow,

this did work. i wish i could understand the code. i was wrong in saying
that the data always comes after the 3rd comma. its not as consistent as i
thought. was wondering if it could instead look for the slash and then go
either 1 or two characters to the left to pick them up. if not, what would
i change in your code that would tell it to do the second comma rather than
the third.etc

again much appreciated.

 

[Ken Snell \(MVP\)]

Are you wanting to do this as a calculated field in a query? Is it ok if
your query calls a VBA function to do the parsing? I ask because "backing up
from the slash" will involve a lot of repeated "find character" parsing
expressions within a single parsing statement otherwise.

(1)
If you were to be looking only for a one- or two-digit number to the right
of the second comma, the expression would be

Replace(Mid(FieldName,
InStr(InStr(1,FieldName,",")+1,FieldName,",")+1,2),"/","",1,1,1)


(2)
If you were wanting to select the one- or two-digit number just to the left
of the slash, the expression would be

Replace(Mid(FieldName,InStrRev(FieldName,",",InStr(1,FieldName,"/",1),1)+1,3),"/","",1,1,1)


(3)
If you were wanting to allow for any number of digits (1, 2, 3, 4, 5, etc.)
to the left of the slash, the expression would be

Mid(FieldName,InStrRev(FieldName,",",InStr(1,FieldName,"/",1),1)+1,InStr(1,FieldName,"/",1)-InStrRev(FieldName,",",InStr(1,FieldName,"/",1),1)-1)


(4)
If you would be willing to use a VBA function to do the parsing, post back
and let me know, and I'll provide suggestions for that approach.