Extract all numbers in an expression

ChewCS

New Member
Joined
Jun 17, 2022
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
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?

Thanks in advance!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
@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.
 
Upvote 0
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:
Upvote 0
@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"
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top