The InStr function and the space character

[delphidestructor]

I’m reading a simple text file and parsing the lines into an Excel 2000 worksheet. I’m using VBA (obviously) and the InStr function to return the positions for the Mid function.

I have tried both of these and other variations of, to return the substring of number characters after the “$”.

Mid(tmpstr, InStr(tmpstr, "$&quot + 1, InStr(tmpstr, Chr(32)) - 1)
Mid(tmpstr, InStr(tmpstr, "$&quot + 1, InStr(tmpstr, “ “) - 1)

An example string:
20701$28623 S 0.23 ----- 0.0387
^
The only chr 32 is here all the others are tabs chr 9
The InStr function will not find the position of the space char. Any suggestions or help would be great. Thanks.

Mike

 

[Schweiger]

try:

Mid(tmpstr, InStr(tmpstr, "$") + 1, InStr(tmpstr, Chr(32)) - InStr(tmpstr, "$"))

This isn't very performant but it shows the trouble. The second argument is the length of the substring and not the position. Perhaps it would be faster like that:

lPos = InStr(tmpstr, "$") + 1
debug.print Mid(tmpstr,lPos,Instr(lPos, tmpstr,Chr(32)) - lPos + 1)

Hope this helps
Andreas

 

[delphidestructor]

OK, you are correct. Thanks for the info. That was a dumb mistake. I knew that. I was so caught up in the InStr function that I wasn't paying any attention to the correct mid function parameters. Mike