Using search range in InStr

[Albert S.]

Hello,

I need to find a string of data using a range of characters. Is it possible
with InStr?

For example:
strPagination = "vii, 39, 24 p."

What I need to find is the position of the first instance of a comma, but
only after a number (and not a letter) - ("9," above).

So I wanted to try:
intPagPos = InStr(1, strPagination, "[0-9],", vbTextCompare)

But this doesn't seem to work. Access2003.

Thanks for any help!

 

[Stuart McCall]

Hello,

I need to find a string of data using a range of characters. Is it
possible
with InStr?

For example:
strPagination = "vii, 39, 24 p."

What I need to find is the position of the first instance of a comma, but
only after a number (and not a letter) - ("9," above).

So I wanted to try:
intPagPos = InStr(1, strPagination, "[0-9],", vbTextCompare)

But this doesn't seem to work. Access2003.

Thanks for any help!

No you can't specify ranges like that. You would need to be using regular
expressions for that to work. This requires adding a library reference and,
if you've not used reg exp before, a steep learning curve.

Thankfully there are other ways. Here's what I'd do:

1 use the split function to create an array of all text items inbetween
commas, like this:

Dim a As Variant
a = Split(strPagination, ",")

2 loop over the array looking for a trailing numeric character:

Dim i As Integer
For i = Lbound(a) To Ubound(a)
If IsNumeric(Right$(a(i), 1)) Then
MsgBox "Found: " & a(i)
End If
Next

Obviously its up to you what you put in place of the msgbox call...

(The above is untested 'air code')

 

[Albert S.]

Excellent! That's exactly what I needed. I can then find the InStr position
and extract what I need.

Thanks again!
--
Albert S.

Hello,

I need to find a string of data using a range of characters. Is it
possible
with InStr?

For example:
strPagination = "vii, 39, 24 p."

What I need to find is the position of the first instance of a comma, but
only after a number (and not a letter) - ("9," above).

So I wanted to try:
intPagPos = InStr(1, strPagination, "[0-9],", vbTextCompare)

But this doesn't seem to work. Access2003.

Thanks for any help!

No you can't specify ranges like that. You would need to be using regular
expressions for that to work. This requires adding a library reference and,
if you've not used reg exp before, a steep learning curve.

Thankfully there are other ways. Here's what I'd do:

1 use the split function to create an array of all text items inbetween
commas, like this:

Dim a As Variant
a = Split(strPagination, ",")

2 loop over the array looking for a trailing numeric character:

Dim i As Integer
For i = Lbound(a) To Ubound(a)
If IsNumeric(Right$(a(i), 1)) Then
MsgBox "Found: " & a(i)
End If
Next

Obviously its up to you what you put in place of the msgbox call...

(The above is untested 'air code')

 

[Stuart McCall]

Excellent! That's exactly what I needed. I can then find the InStr
position
and extract what I need.

Thanks again!

<snip>

You're welcome.

No need to parse the string for what you want. It's all in the array waiting
for you (minus the commas).

 

[Albert S.]

Ok, I have been working with this and I need the loop to stop after the first
Numeric-Comma combination. So for example:
21, 13, 3x11 p. - would stop after 21
xvi, 25, 10 p. - would stop after 25

Thanks for the help!
--
Albert S.

Here is the code:
strPagination = "21, 13, 3x11 p."
Dim a As Variant
a = Split(strPagination, ",")
Dim i As Integer
For i = Lbound(a) To Ubound(a)
If IsNumeric(Right$(a(i), 1)) Then
MsgBox "Found: " & a(i)
End If
Next

 

[Stuart McCall]

Ok, I have been working with this and I need the loop to stop after the
first
Numeric-Comma combination. So for example:
21, 13, 3x11 p. - would stop after 21
xvi, 25, 10 p. - would stop after 25

Thanks for the help!
--
Albert S.

Here is the code:
strPagination = "21, 13, 3x11 p."
Dim a As Variant
a = Split(strPagination, ",")
Dim i As Integer
For i = Lbound(a) To Ubound(a)
If IsNumeric(Right$(a(i), 1)) Then
MsgBox "Found: " & a(i)
End If
Next

That's easy, just exit the loop as soon as the first one is found:

strPagination = "21, 13, 3x11 p."
Dim a As Variant
a = Split(strPagination, ",")
Dim i As Integer
For i = Lbound(a) To Ubound(a)
If IsNumeric(Right$(a(i), 1)) Then
MsgBox "Found: " & a(i)
Exit For '<== Note
End If
Next

 

[Albert S.]

Ok, great! Yes, that did the trick. I have a few more iterations to check,
but I think I have about 95% of them covered. Thanks again for all the help
on this!