CODEWITHSUNDEEP
excelvbarange
n00b.exe
n00b.exe

Reputation: 287

VBA Split Range Expression

Assuming I have some cells with Expressions filled like this :

Dim Sample1 As String
Sample1 = "1-4,5+6,9,12-20"
Dim Sample2 As String
Sample2 = "1-7,8,30,20+21,45"
Dim Sample3 As String
Sample3 = "1-7,8,11-17"

What's the best way to convert this expression into an array which contains the specified numbers? So e.g for Sample1 the array for this expression should conmtain the values :

1,2,3,4,5,6,9,12,13,14,15,16,17,18,19,20

My first attempt would be to split the expression by commas and then try to figure out whats left and right from the "-" and the "+" but maybe there is a better way? Can you give me some advice how to do this best?

Upvotes: 0

Views: 544

Answers (1)

user4039065
user4039065

Reputation:

Replace any - with + so they all react the same to a second nested split.

Option Explicit

Function udf_All_Nums(str As String, Optional delim As String = ", ")
    Dim tmp As String, val As Variant, vals As Variant
    Dim a As Long, i As Long

    vals = Split(str, Chr(44))
    For a = LBound(vals) To UBound(vals)
        vals(a) = Replace(vals(a), Chr(45), Chr(43))
        val = Split(vals(a), Chr(43))
        For i = val(LBound(val)) To val(UBound(val))
            tmp = tmp & delim & i
        Next i
    Next a

    udf_All_Nums = Mid(tmp, Len(delim) + 1) 'Join(vals, delim)
End Function

enter image description here

Upvotes: 1

Related Questions