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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,780
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,780
Office Version
365, 2019, 2016
Platform
Windows
───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂ ...
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂
 

Watch MrExcel Video

Forum statistics

Threads
1,102,696
Messages
5,488,360
Members
407,633
Latest member
ps01

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top