Parsing variable length strings

[puffthemagicdragon]

I have some output from a piece of code which produces five integers, randomly selected between 1 and 59; each is comma delimited like so:

,21,19,54,16,59
,45,31,44,6,58
,54,31,3,2,24
,40,3,13,34,46
,52,36,7,17,56
,34,14,33,35,56

I need to get each integer into a separate field, but can't figure a way to parse them out as, being random, there is no consistency to the start/end positions.

I suppose it might be possible to test each possible position permutation with an IF LOOP, but surely there is a more efficient option?

Thanks in advance for your help.

 

[RuralGuy]

What do you mean when you say "...get each integer into a separate field..."? In a table?

 

[George145]

Since there is a separator character between the numbers, while assuming that your string starts with a comma and that there are only numbers and commas, you can do something like this:

Sub RetrieveIntegers(FiveIntegers as String)

Dim CurrentChar as String
Dim i,j, IntArray(4) as Integer
Const SeparatorChar = ","

For j=0 to 4
    IntArray(j)=0
Next j

j=-1
For i=1 to Len(FiveIntegers)
    CurrentChar=Mid(FiveIntegers,i,1)
    if CurrentChar= SeparatorChar then
        j=j+1
    Else
        IntArray(j)=IntArray(j)*10+CInt(nz(CurrentChar,0))
    End If
Next i

End Sub

I hope there are no syntax errors since I cannot run this code right now, but it is supposed to fill IntArray with the five integers. After that, you can store them anywhere you want. It is easy to modify this code in case there are also other characters in the string (i.e. spaces, unwanted letters, etc.). You may also add the appropriate error handling.

 

[Guus2005]

Use split() for that purpose.

Enjoy!

 

[George145]

wow!

I think I have to study a little about string handling in vba - for not reinventing the wheel every time

Thanks Guus