InStr or InStrRev - Extract data from String

Question

I have looked at many of the Q&As on InStr and InStrRev to "find" a specific string/character within a string.

InStr (Start, StringCheck, StringMatch, Compare)

InStrRev (StringCheck, StringMatch, Start, Compare)

My question is when specifying the StringMatch, can wildcards be used?  If so, where can I find them and use them?

I need to extract part of a string from a field, each StringCheck varies in length & the starting varies as well.  There is not a single character I can used, yet I want to extract the part of the string to the LEFT of "####" (left most string of four numbers) or ##########_ (string of 10 numbers followed by an underscore).  Below are some examples of before (left column) and desired results (right column):

Original	Desired
.../remitsplit/legacy/Legacy1106120945_20121106...txt	.../remitsplit/legacy/Legacy
.../pbremit/load/ABC835PB1106120945_20121106...txt	.../pbremit/load/ABC835PB
.../hbremit/load/ABC835HB1106120945_20121106...txt	.../hbremit/load/ABC835HB
.../remitsplit/legacy/LegacyCFM1106120945_20121106...txt	.../remitsplit/legacy/LegacyCFM
.../remitsplit/legacy/rps/rps835HB1106120945_20121106...txt	.../remitsplit/legacy/rps/rps835HB
.../remitsplit/legacy/Legacy1106120945_20121106...txt	.../remitsplit/legacy/Legacy
.../hbremit/load/ABC835HB1106120945_20121106...txt	.../hbremit/load/ABC835HB
.../hbremit/load/ABC835HB1106120945_20121106...txt	.../hbremit/load/ABC835HB

This information is stored in MyTable, Field1, and Field2

Thank you in advance for your time and assistance.

Sincerely,

MJ

Answer 1

Try this --

Left([Orignial],InStr([Orignial],"_")-11)

Answer 2

Karl,

Your reply was spot-on in coming up with a key to get my desired output.

Thank you,

MJ

Answer 3

This is the sort of problem that regular expressions are designed to solve.  There is nor Regular Expression object built into Access VBA, but you can set a reference to "Microsoft VBScript Regular Expressions 5.5" and then use the RegExp object and associated classes. 

I have to admit that I myself haven't used regular expressions much, so I can't give you a lot of further information about them, but there seems to be a lot if information out there on the Web.

If you don't want to use a regular expression, you can of course write some a routine of your own to parse out the text you want.  Such a routine might well make use of InStr and InStrRev, but those functions aren't able to do what you want all by themselves.

Answer 4

InStr() and InStrRev() do not recognize wildcard characters.  I think what you would be looking for in order to truly use wildcards is the "Like" statement:

If strMyString LIKE "##########_" then

           'There are 10 numbers and then a hyphen in strMyString

end if

In regards to parsing your specific strings, when it is the "part of the string to the LEFT of ####", is there ever an underscore in that scenario?  If not, I would first look for an underscore.  If one is found, it sounds like you can just subtract 11 from the position of the underscore to get the last character of the string you're looking for.  In the other case, I think you might have to resort to looping through your string (or just the relevant part) until you identify the four consecutive numbers, then return the part of the string before that.

I can provide sample code if it sounds this would work for you.