Instr() code not giving correct result

[Angi]

I'm trying to parse a number out of a size field. The data in the
field is ## x ## x ##. There could be different lengths of numbers, so
I'm trying to use the Mid with the Instr method.

Here's my code:
Me.txtText = Mid(cboSize.Column(2), InStr(1, cboSize.Column(2), "x", 1)
+ 2, InStr(InStr(1, cboSize.Column(2), "x", 1), cboSize.Column(2), "x",
1) - 2)


The weird thing is if the field is
60 x 40 x 24 the result is 40 (correct)
20 x 0.5 x 63 the result is 0. (incorrect)

Why is it acting like it's using a specific numbers of spaces instead
of looking for the 2nd x??

TIA!

 

[Ken Snell [MVP]]

You need to start the InStr(InStr...etc. part on the character after the
nested InStr value.

Try this:

Me.txtText = Mid(cboSize.Column(2), InStr(1, cboSize.Column(2), "x", 1)
+ 2, InStr(InStr(1, cboSize.Column(2), "x", 1)+1, cboSize.Column(2), "x",
1) - 2)

 

[Angi]

Ken,
Now it's giving me everything after the first X and space.

 

[Ken Snell [MVP]]

Try this:

Me.txtText = Mid(cboSize.Column(2), InStr(1, cboSize.Column(2), "x", 1) + 2,
InStrRev(cboSize.Column(2), "x") - InStr(1, cboSize.Column(2), "x", 1) - 2)

 

[Angi]

OK...that worked AND I learned something new. I looked up InStrRev in
the help and I now understand the formula. I think in numbers so this
is what I get:

InStrRev(cboSize.Column(2), "x") = 10

- InStr( 1, cboSize.Column(2), "x", 1) = 4

- 2) = -2

length = 10-4-2 = 4 chars

Had to change -2 to -3 to get rid of the trailing space but I could
handle that!
Thanks so much!

 

[Ken Snell [MVP]]

You also can use Trim function to remove leading and trailing spaces; LTrim
to remove leading spaces; and RTrim to remove trailing spaces.

 

[Angi]

Ken,
I use Trim a lot, but I thought since it was as simple as changing a
number rather than adding more code, I'd do that. Thanks for the
advice though! You've been most helpful!

BR,
Ang

 

[Ken Snell [MVP]]

You're welcome.