Split Function

[ECEstudent]

Hi, I have values in an array that are formatted like this:

{00123-2396667, 46932-780, 8708-0987, 456-22}

What I am trying to do is break/split the array into 2 arrays so the result would look like this:

{00123, 46932, 8708, 456}

AND

{2396667, 780, 0987, 22}

Does anyone know how to do that?

Thank you

-Laura

 

[billmeye]

Here is a function that can preform that task. What you do with the results is up to you.

Function DoubleSplit(StrVal As String)

'example
'Call DoubleSplit("1-a,2-b,3-c,4-d")

Dim PreVal() As String, PostValCol1() As String, PostValCol2() As String
Dim x As Integer, PreArray As String, PostArray As String
ReDim PreVal(1000) As String, PostValCol1(1000) As String, PostValCol2(1000) As String

PreVal() = Split(StrVal, ",")
For x = 0 To UBound(PreVal)
PostValCol1(x) = Left(PreVal(x), InStr(1, PreVal(x), "-") - 1)
PostValCol2(x) = Right(PreVal(x), InStr(1, PreVal(x), "-") - 1)
    'just for demonstration
    If x = 0 Then
    PreArray = PostValCol1(x)
    PostArray = PostValCol2(x)
    Else
    PreArray = PreArray & "," & PostValCol1(x)
    PostArray = PostArray & "," & PostValCol2(x)
    End If
Next x
'now do something with these values
Debug.Print "Orig Array: " & StrVal
Debug.Print "New Array1: " & PreArray
Debug.Print "New Array2: " & PostArray
End Function

 

[ECEstudent]

Keeps saying type mismatch on this:

PreVal() = Split(StrVal, ",")

 

[billmeye]

How are passing your array? Please show me how you are using the function.

 

[ECEstudent]

        Set rop = CurrentDb.OpenRecordset("Select OrderNumber, ItemNumber From dbo_EntryStructure Where (ProductNumber = '" & txtPartNumber & "')")
        
        While rop.EOF = False
            ReDim Preserve ArrRepOrder(j)
            ReDim Preserve ArrItem(j)
            ArrRepOrder(j) = rop.Fields("OrderNumber") 'Collect RepId
            ArrItem(j) = rop.Fields("ItemNumber") 'Collect Item
            j = j + 1
            rop.MoveNext
        Wend
        L = ArrRepOrder

So First I'm putting in values into an array. The array is declared as a string. I now want to use the split function on that array but I don't know how.

 

[ECEstudent]

I tried:

PreVal() = Split(ArrRepOrder, "-")

PreVal() = Split(ArrRepOrder, "-")

PreVal = Split(L, ",")



But it doesn't seem to be able to recognize any

 

[ECEstudent]

I would appreciate any help you guys. This is the last part in my code that I need to incorporate to make the whole thing work. Please help.

 

[billmeye]

Since you are passing an array, try this code:

Function DoubleSplit(MyArray As Variant)
Dim PostValCol1(1000) As String, PostValCol2(1000) As String
Dim x As Integer, PreArray As String, PostArray As String, strval As String

'example
'Call DoubleSplit(YourArray)

For x = 0 To UBound(MyArray)
PostValCol1(x) = Left(MyArray(x), InStr(1, MyArray(x), "-") - 1)
PostValCol2(x) = Right(MyArray(x), InStr(1, MyArray(x), "-") - 1)
    'just for demonstration
    If x = 0 Then
    PreArray = PostValCol1(x)
    PostArray = PostValCol2(x)
    strval = MyArray(x)
    Else
    PreArray = PreArray & "," & PostValCol1(x)
    PostArray = PostArray & "," & PostValCol2(x)
    strval = strval & "," & MyArray(x)
    End If
Next x
'now do something with these values
Debug.Print "Orig Array: " & strval
Debug.Print "New Array1: " & PreArray
Debug.Print "New Array2: " & PostArray
End Function

The previous code I gave you assumed you were passing a string of comma separated values.

 

[ECEstudent]

Thanks for the reply billmeye. I tried it but now it says that subscript is out of range?

 

[pr2-eugin]

Which line are you getting the error?

 

[ECEstudent]

Oh it works now! I just forgot to include one of the lines mentioned.

But it's only extracting the last 4 digits of the array. For example:

{018-J018325096}

Is now:

{018} = Array 1
{5096} = Array 2

I need it to be :

{018} = Array 1
{J018325096} = Array 2

 

[pr2-eugin]

Try changing the Right function to Mid..

PostValCol2(x) = Mid(MyArray(x), InStr(MyArray(x), "-") + 1)

 

[billmeye]

Replace:

PostValCol2(x) = Right(MyArray(x), InStr(1, MyArray(x), "-") - 1)

With:

PostValCol2(x) = Mid(MyArray(x), InStr(1, MyArray(x), "-") + 1, Len(MyArray(x)))

 

[ECEstudent]

Thank you guys so much. It works great!

 

[pr2-eugin]

Glad we could be of help ECEstudent..

@billmeye, no offense but, you do not need the last Len in the Mid function.. As it is an optional value, left blank will return (ultimately) until the end o the String.. Using a Len() is another computation that you force on that LOC...

 

[billmeye]

Thanks for the info Paul.