Extract all numbers in an expression

[ChewCS]

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!

 

[kvsrinivasamurthy]

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]

See if this does what you are after.

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]

Sorry for not putting quotes to the code.
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

 

[ChewCS]

Ap

Sorry for not putting quotes to the code.
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

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]

@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]

Try this

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

]

 

[ChewCS]

@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]

Add this line

ReDim Preserve ary(1 To 1, 1 To X)
'enter array values in the range

after
Next T

 

[Alex Blakenburg]

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.

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