Extract numbers from string containing dashes and commas

Overside

New Member
Joined
Jul 24, 2018
Messages
4
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!
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,758
Office Version
365, 2019, 2016
Platform
Windows
Welcome to the forum.

Try this...

Code:
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.
 
Last edited:

Overside

New Member
Joined
Jul 24, 2018
Messages
4
The code worked perfectly, even better that it displays the extracted numbers on a row actually. Thanks!
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,758
Office Version
365, 2019, 2016
Platform
Windows
───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂ ...
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂
 

Watch MrExcel Video

Forum statistics

Threads
1,099,684
Messages
5,470,121
Members
406,681
Latest member
sachinmasurkar

This Week's Hot Topics

Top