Extract numbers from string containing dashes and commas

[Overside]

Hello everyone,

Just created my account as this is the first time I'm having to ask a specific question since I couldn't find a solution from just searching or googling...

I'm presented with a list of strings that include a combination of numbers, commas and dashes that can look like the following:

1,2,3
1,4-6
10-25,44

From these I have to extract all individual numbers into a column. For example:

1,2,3 becomes a list with {1,2,3}
1,4-6 becomes a list with {1,4,5,6}
10-25,44 becomes a list with {10,11,...24,25,44}

I'm looking for either a VBA or non VBA solution, any help or guidance is appreciated!

 

[lrobbo314]

Welcome to the forum.

Try this...

Sub Extract()
Dim AR()
Dim SP() As String
Dim D() As String
Dim cCnt As Long


cCnt = 2


AR = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Value


For i = 1 To UBound(AR)
    SP = Split(AR(i, 1), ",")
        For j = 0 To UBound(SP)
            If InStr(SP(j), "-") > 0 Then
                D = Split(SP(j), "-")
                    For k = D(0) To D(1)
                        Cells(i, cCnt).Value = k
                        cCnt = cCnt + 1
                    Next k
            Else
                Cells(i, cCnt).Value = SP(j)
                cCnt = cCnt + 1
            End If
        Next j
        cCnt = 2
Next i


End Sub

The code assumes that your data is in column A beginning at cell A1. You will need to change that part of the code if your data is somewhere else.

 

[Overside]

The code worked perfectly, even better that it displays the extracted numbers on a row actually. Thanks!

 

[lrobbo314]

───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂ ...
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂