Split cell value into numeric array

Question

I have a cell containing something like "12_34_56"

I used:

MyNumbers = Split(Cells(1, 1).Value, "_")

to split the numeric values into an array , but the result is an array of strings!

I know that I can use CInt on every value of the array but isn't any way to obtain a numeric array directly?

thanks in advance

may I ask what the purpose of splitting it directly into an Integer array is? — user2140173
– user2140173, Commented Dec 5, 2014 at 10:36
Split() function always returns a String based array. There is no direct .Cast<int> in VBA. You would need a wrapper to convert that String based array to an Integer based one. — user2140173
– user2140173, Commented Dec 5, 2014 at 10:39
1) Only avoiding to use CInt on every value on extracting it from array. 2) So using CInt on every value is the simplest solution? — genespos
– genespos, Commented Dec 5, 2014 at 10:45
I would say yes, CInt() on each value is the simplest in this scenario. — user2140173
– user2140173, Commented Dec 5, 2014 at 10:53
Ok, sometimes trying to simplify we make things more complex ... Thanks to everybody — genespos
– genespos, Commented Dec 5, 2014 at 10:58

Legenis · Accepted Answer · 2016-10-06 13:50:48Z

3

This worked great for me and was very easy to set up! I will use cell A1 as the example with the string of numbers.

Dim strarray as Variant
Dim intarray as Variant
Dim x as Long

strarray = Split(Range("A1").Value, "_")
ReDim intarray(LBound(strarray) to UBound(strarray))
For x = LBound(strarray) To UBound(strarray)
    intarray(x) = CInt(strarray(x))
Next x

answered Oct 6, 2016 at 13:50

Legenis

711 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aiken · Accepted Answer · 2014-12-05 12:39:11Z

2

As far as I'm aware it's not possible to convert one type of array into another type with a single call, but if you're going to be doing this conversion in multiple locations the obvious option is to write a function that returns the desired result, as below:

Public Function SplitIntegers(StringToSplit As String, Sep As String) As Variant
    Dim arrStrings() As String
    Dim arrIntegers() As Integer
    Dim i As Long

On Error GoTo Err_SplitIntegers
    arrStrings = Split(StringToSplit, Sep)
    ReDim arrIntegers(LBound(arrStrings) To UBound(arrStrings))

    For i = LBound(arrStrings) To UBound(arrStrings)
        arrIntegers(i) = CInt(arrStrings(i))
    Next i

    SplitIntegers = arrIntegers
    Exit Function

Err_SplitIntegers:
    Select Case Err.Number
        Case 13 'Type Mismatch Error: StringToSplit contains non-numeric substrings
            On Error GoTo 0
            Err.Raise 9114, "SplitIntegers", _
                      "SplitIntegers failed: substring '" & arrStrings(i) & "' of string '" & StringToSplit & "' is not numeric"
        Case Else 'Unhandled error, return to calling code
            Dim iErrNum As Integer, strErrDesc As String
            iErrNum = Err.Number
            strErrDesc = Err.Description
            On Error GoTo 0
            Err.Raise iErrNum, "SplitIntegers", strErrDesc
    End Select
End Function

When you need this functionality you can just call this function as a one-liner as you would the Split function.

Dim arrMyInts() As Integer
arrMyInts = SplitIntegers(Cells(1,1).Value, "_")

edited Dec 5, 2014 at 12:39

answered Dec 5, 2014 at 11:09

Aiken

2,6682 gold badges20 silver badges25 bronze badges

5 Comments

Aiken Over a year ago

@vba4all Is it really worth handling errors within the function? If I ran (or could possibly run) the statement SplitIntegers("Bob_Alice_Fred_Emily", "_") I'd fully expect a Type Mismatch error and would include the error handling for that in the calling code, where I can modify or respond to the invalid input with a better sense of context. Validating this kind of input is probably a job for regex as it is.

user2140173 Over a year ago

Any function is rather expected to handle the errors that can be caused by the function's body by the function. The Type-mismatch doesn't come from passing in a wrong type argument but by the Cint inside the functions body. If you allow someone to pass a string and they do that - they wouldn't know what theyve done wrong by passing a string - A simple on error goto <label> would do here I believe

Aiken Over a year ago

@vba4all The problem here is I can't think of a meaningful error resolution the function could provide without a calling context. At best I'd go to the label and then have the error handling raise an error with a more meaningful description which is then still up to the calling code to handle. I sure as hell wouldn't want to see a call to MsgBox in this function's error handling (or anywhere in this function), the end user might not be the programmer after all.

user2140173 Over a year ago

Ok, if it was me I probably want to show the position of where the Cint() failed at so anyone passing an argument can quickly correct it. Return an empty array if CInt failed. I know VBA error handling is poor, but still its worth doing something about errors you can catch

Aiken Over a year ago

@vba4all How's that? It still causes an error if the StringToSplit argument is invalid, but at least it's got a more meaningful description and a non-standard error code.

Collectives™ on Stack Overflow

Split cell value into numeric array

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related