# Extract numbers from string containing dashes and commas

#### Overside

##### New Member
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!

### 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
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
The code worked perfectly, even better that it displays the extracted numbers on a row actually. Thanks!

#### lrobbo314

##### Well-known Member
───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂ ...
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂

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...