# Extract all numbers in an expression

#### ChewCS

##### New Member
Hi, I would like to seek assistance from this forum in a certain task.

I have a cell with an expression that includes ranges of numbers. Something like "1-3,13,15-17". I would like to have a VBA function that extracts all the numbers within this single expression and store them in an array. Something like

array(1,1) is 1, array(1,2) is 2, array(1,3) is 3, array(1,4) is 13, array(1,5) is 15, array(1,6) is 16, array(1,7) is 17.

Is it possible?

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### kvsrinivasamurthy

##### Well-known Member
Try this code.
M is an array.
ary also an array.
Sub GetArray()
Dim M
Dim S As String
Dim T As Long
S = Replace(Range("A2"), "-", ",")
M = Split(S, ",")
ReDim ary(1 To 1, 1 To UBound(M) + 1)
For T = 0 To UBound(M)
ary(1, T + 1) = Val(M(T))
Next T
'enter array values in the range
Range("c2").Resize(1, UBound(M) + 1) = ary
End Sub

#### Alex Blakenburg

##### MrExcel MVP
See if this does what you are after.

VBA Code:
``````Sub fillsequence()

Dim NoRanges As Variant
Dim FirstLast As Variant
Dim outArr() As Long
Dim i As Long, j As Long, outRow As Long

NoRanges = Split(Range("A2"), ",")
FirstLast = Split(NoRanges(UBound(NoRanges)), "-")
ReDim outArr(1 To FirstLast(UBound(FirstLast)), 1 To 1)

For i = 0 To UBound(NoRanges)
FirstLast = Split(NoRanges(i), "-")
For j = FirstLast(0) To FirstLast(UBound(FirstLast))
outRow = outRow + 1
outArr(outRow, 1) = j
Next j
Next i

Range("B2").Resize(outRow).Value = outArr

End Sub``````

#### kvsrinivasamurthy

##### Well-known Member
Sorry for not putting quotes to the code.
Try this code.
M is an array.
ary also an array.
VBA Code:
``````Sub GetArray()
Dim M
Dim S As String
Dim T As Long
S = Replace(Range("A2"), "-", ",")
M = Split(S, ",")
ReDim ary(1 To 1, 1 To UBound(M) + 1)
For T = 0 To UBound(M)
ary(1, T + 1) = Val(M(T))
Next T
'enter array values in the range
Range("c2").Resize(1, UBound(M) + 1) = ary
End Sub``````

#### ChewCS

##### New Member

Ap
Sorry for not putting quotes to the code.
Try this code.
M is an array.
ary also an array.
VBA Code:
``````Sub GetArray()
Dim M
Dim S As String
Dim T As Long
S = Replace(Range("A2"), "-", ",")
M = Split(S, ",")
ReDim ary(1 To 1, 1 To UBound(M) + 1)
For T = 0 To UBound(M)
ary(1, T + 1) = Val(M(T))
Next T
'enter array values in the range
Range("c2").Resize(1, UBound(M) + 1) = ary
End Sub``````
Appreciate your help! I would like to confirm that the moment the Replace function is called, VBA would not know there is a range of numbers, but treats every number as an individual number? i.e. "1-3,13,15-17" gets converted to "1,3,13,15,17"? And 2 and 16 are excluded from the final array?

#### Alex Blakenburg

##### MrExcel MVP
@ChewCS, did you try my suggestion ?
PS: I am writing the output to B2 so take a backup of your spreadsheet first or work on a copy.

#### kvsrinivasamurthy

##### Well-known Member

Try this

VBA Code:
``````Sub Macro1()
Dim M, N, P
Dim S As String
Dim T As Long, Ta As Long, X As Long

S = Range("A4")
P = Split(Replace(S, "-", ","), ",")
ReDim ary(1 To 1, 1 To Val(P(UBound(P))))
M = Split(S, ",")
For T = 0 To UBound(M)
If InStr(1, M(T), "-") > 0 Then
N = Split(M(T), "-")
For Ta = Val(N(0)) To Val(N(UBound(N)))
X = X + 1
ary(1, X) = Ta
Next Ta
Else
X = X + 1
ary(1, X) = Val(M(T))
End If
Next T
'enter array values in the range
Range("A4").Offset(0, 1).Resize(1, X) = ary
End Sub``````
]

Last edited:

#### ChewCS

##### New Member
@ChewCS, did you try my suggestion ?
PS: I am writing the output to B2 so take a backup of your spreadsheet first or work on a copy.
Hi Alex, I managed to make it work, but the numbers come out in a column instead of a row, which is my intention.

Also, if I have an existing array instead of input at "A2", will the NoRanges be defined as below?

NoRanges = Split(inArray(k, 1), ",") 'looping over k

I got an out of bounds statement at "outArr(outRow, 1) = j"

#### kvsrinivasamurthy

##### Well-known Member
VBA Code:
``````ReDim Preserve ary(1 To 1, 1 To X)
'enter array values in the range``````
after
Next T

#### Alex Blakenburg

##### MrExcel MVP
Hi Alex, I managed to make it work, but the numbers come out in a column instead of a row, which is my intention.

To swap the output to being in a row is a simple change. You can use the code below.
(just made outArr a single dimension array)

Having your source coming from an array is more involved.
To start with you need to know whether it is continuing on, on the 1 row.
As you have found out this will blow out the outArr. It will now have to work out a maximum size based on an additional multiplier.

I am login off for the night and can have a look tomorrow but I would need sample data for your array and also how it is being populated. And an example of how you want the output to look.
Is the array being populated from a range ?
Don't oversimplify the data, it has to be a reasonable representation of the real data.

VBA Code:
``````Sub fillsequence_v2_row()

Dim NoRanges As Variant
Dim FirstLast As Variant
Dim outArr() As Long
Dim i As Long, j As Long, outRow As Long

NoRanges = Split(Range("A2"), ",")
FirstLast = Split(NoRanges(UBound(NoRanges)), "-")
ReDim outArr(1 To FirstLast(UBound(FirstLast)))

For i = 0 To UBound(NoRanges)
FirstLast = Split(NoRanges(i), "-")
For j = FirstLast(0) To FirstLast(UBound(FirstLast))
outRow = outRow + 1
outArr(outRow) = j
Next j
Next i

Range("B2").Resize(1, outRow).Value = outArr

End Sub`````` Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,168,153
Messages
5,857,676
Members
431,891
Latest member
shirazx3 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back