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

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
Joined
Nov 6, 2013
Messages
860
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
Joined
Feb 23, 2021
Messages
4,120
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 6, 2013
Messages
860
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
Joined
Jun 17, 2022
Messages
15
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Feb 23, 2021
Messages
4,120
Office Version
  1. 365
Platform
  1. Windows
@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
Joined
Nov 6, 2013
Messages
860

ADVERTISEMENT

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
Joined
Jun 17, 2022
Messages
15
Office Version
  1. 2016
Platform
  1. Windows
@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
Joined
Nov 6, 2013
Messages
860
Add this line
VBA Code:
ReDim Preserve ary(1 To 1, 1 To X)
'enter array values in the range
after
Next T
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
4,120
Office Version
  1. 365
Platform
  1. Windows
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
 
Master Excel Bundle

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

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