Pull out part of the string within the cell

[lwilt]

I'm trying to find a function in access that could do the following:

ZAB103-3012
ZAF405-HD-0001

Turn those cells into:

AB103
AF405-HD

Pulling everything to the right of the Z in the last step is the easy part but I can't figure out what function would be able to find the last "-" in the cell and pull everything to the left of it. Please tell me how this can be done.

thanks
-Logan

[RuralGuy]

Have you looked at the InStrRev() function?

[lwilt]

Couldn't get it to work but I may have tried an incorrect formula....any idea what the formula should be?

[RuralGuy]

What did you try?

[lwilt]

InStrRev([Cell being Searched],"-")

So in the example it returns a 5 but then how would I use the midpoint function to pull everything left of that number that is returned because the length of each cell is different

[lwilt]

OHH never mind I think that should work. I didn't realize it was giving me the character number going left to right so now I can just use the midpoint function going from 2 to the number InStrRev() returns.

Does that sound right?

[RuralGuy]

How about: Mid([Cell being Searched], 2, InStrRev([Cell being Searched], "-") - 2)