I need help parsing a string on VBA. I have extremely limited coding experience, so it may be something simple I just can't figure out. Here's the problem:

I've got strings like "ABX.HE.A.07-1 81-24 3/4 / 82-24"
Where the first part is an index name, there is a variable number of spaces, then there is a bid followed by " / " and then an ask price.

The idea is to break it into the three components (index, bid, ask). The price might have a fractional addition (3/4, etc.) or it might not.

What I want to do is to step through each character and ask "Is this a space?" I figure an IF statement will do it, but I don't know how to step through individual characters.

Any advice?

7/15/2007 6:02:50 PM

i doubt using mid() is very efficient, but id imagine it would work for what you are describing.

mid(string,starpos,length)


that said, im sure you'd be better off piecing it out by / using instr/splitstr/etc or something and then doing a check to see if there were 4 pieces instead of 3 to recombine the price if necessary. shrug. ill let you figure it out.



[Edited on July 15, 2007 at 6:10 PM. Reason : i forget the most efficient character-based piece function off the top of my head]

7/15/2007 6:06:07 PM

Sounds like a job for a regular expression. IDK if VBA does that, but I'd be kinda surprised if it didn't.

7/15/2007 6:06:41 PM

Thanks for the help. I'm working right now on sorting out the bid and the ask, and I think InStr is going to work ok.

The problem with breaking it down and counting the segments is that I have to be able to tell if the bid or ask, or both has the fractional price.

I do appreciate the help guys.

7/15/2007 6:22:15 PM

shrug, you can tell instr where to start in a string so you could count up the occurances and then piece it out accordingly. again, probably not the prettiest solution, but i dont code heavily in basic.

7/15/2007 6:40:41 PM

you may want something like below:

Quote :

" testString = "ABX.HE.A.07-1 81-24 3/4 / 82-24" 'string to parse parsedString = Split(testString, " / ") 'split string on the " / " askprice = parsedString(1) parsedString2 = Split(parsedString(0), " ") 'split remaining on spaces, this will return index at element 0 indexName = parsedString2(0) parsedString3 = Split(parsedString(0), indexName) 'split the original split on the index name, this will return only bid bidPrice = Trim(parsedString3(1)) ' trim all leading and trailing spaces from the bid price MsgBox indexName MsgBox bidPrice MsgBox askprice "

that's actual VBA code I just tested and works. you may want to change variable names

[Edited on July 15, 2007 at 8:36 PM. Reason : .]

7/15/2007 8:35:26 PM

Thanks HaLo. I appreciate the help.

7/16/2007 7:52:48 PM

for future reference, learn how to use regular expressions. they are much faster at parsing strings than any of the built-in string handling functions. Also, Trim$(), InStr$(), Mid$(), Left$(), Right$() are faster than their variant counterparts ( Trim(), InStr(), etc. ) on b-strings.

7/16/2007 11:30:10 PM