Full Version: Formula
vbosmiya
I have series of Part ID and want to extract last few letters. I was thinking of the using the RIGHT formula but the thing is len of Part ID is different.

Ex -

DS-PEBBLESAND-20-54-50
DS-PEBBLESAND-20-54-60M
DS-PEBBLESAND-20-60-100
DS-PEBBLESAND-20-60-25
DS-PEBBLESAND-20-60-50
DS-PEBBLESAND-20-63-60M
DS-PIQUE-15-48-100
DS-PIQUE-15-48-25
DS-PIQUE-15-48-50
DS-PIQUE-15-54-100

In above list, I want to use right formula and want to get anything after 20 or 15.

How can I do this ?

Thank you,

Vipul
fkegley
The InStr function can find the 20 or 15 and the Mid\$ can fetch the part of interest.

Expr1:Iif(InStr(1, [Part ID], "20") > 0, Mid\$(InStr(1, [Part ID]) + 2, Iif(InStr(1, [Part ID], "15") > 0, Mid\$(InStr(1, [Part ID], "15") + 2, ""))))

I think I have the right number of (s and )s but would not be surprised if I didn't.
niesz
If the 3rd component is not always 15 or 20, try this:

Mid([YourField], InStr(InStr(InStr([YourField], "-") + 1, [YourField], "-") + 1, [YourField], "-") + 1)

This should return everything after the third hyphen.

EDIT: Frank, I'm not sure I like your method. What if the first section of the string was like:

DS-ROSE20RED-20-63-60M
vbosmiya
Thats exactly what I was looking for.

Thanks a lot.

Vipul
niesz
I'm not sure which version you used, but Frank and I are glad to help.
vbosmiya
I have used your version bcoz it fits good with all the Part ID in my database.

Thanks to you and Frank too.

Vipul
fkegley
People who wear brassieres on their heads shouldn't throw rocks!

Sorry Walter, I couldn't resist. You're about my code tho I should probably have looked for -20 and -15.
niesz
HA HA HA. I think the southern air is getting to you. Must be the jet stream.
fkegley
It's some kind of stream all right, but I don't think it's jet!