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!

 

[ChewCS]

Add this line

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

after
Next T

Thanks, it works I suppose ary now holds the array of numbers.

If I have an existing array that is holding the strings, will S be defined like this? Assuming "1-3,13,15-18" is at inArray(5 , 1),

S = inArray(5 , 1)

 

[ChewCS]

Hi guys, thank you so much for your advice I too am resting. Will provide the code as soon as I can. Basically, I am reading a file with rows of data, and one such column consists of the strings that I mention. And I wish to convert this unusable string into an array of integers. The size of this array is definite, because the integers from the string can only go up to 18.

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

 

[kvsrinivasamurthy]

Is your problem is not solved. I am not clear about your post #11.

 

[rlv01]

Perhaps you could use the XL2BB add-in to give us an example of what your data looks like and what the result you want looks like?

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

Example:

Book1
	A	B	C	D	E	F	G	H	I
1	File Data	Result 1	Result 2->
2	1-3,13,15-17	1,2,3,13,15,16,17	1	2	3	13	15	16	17
Sheet1

Sub MakeArray()
    
    Dim WS As Worksheet
    Dim I As Long, J As Long
    Dim NS As String
    Dim SA, SB, MyArray
    
    NS = Range("A2").Value
    
    SA = Split(NS, ",")
    NS = ""
    For I = 0 To UBound(SA)
        If InStr(SA(I), "-") > 0 Then
            SB = Split(SA(I), "-")
            For J = SB(0) To SB(1)
                NS = NS & J & ","
            Next J
        Else
            NS = NS & SA(I) & ","
        End If
        Erase SB
    Next I
    Erase SA
    
    NS = Left(NS, Len(NS) - 1)
    MyArray = Split(NS, ",")
    
    'Result 1
    Range("B2").Value = NS
    
    'Result 2
    For I = 0 To UBound(MyArray)
        Range("C2").Offset(0, I).Value = MyArray(I)
    Next I
End Sub

 

[Alex Blakenburg]

Will provide the code as soon as I can. Basically, I am reading a file with rows of data, and one such column consists of the strings that I mention. And I wish to convert this unusable string into an array of integers. The size of this array is definite, because the integers from the string can only go up to 18.

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

You now have 3 options for step 1 but we look forward to getting more information for you for step 2 eg existing code, examples of input and output

Based on you already having an array using a line counter of k and that you are processing lines 1 at a time you could as you suggested but the whole code inside a loop that increments using "k".Replacing where we are referring to a call with you array element value. In my case that includes having the Redim command inside your k loop since it needs to be evaluated on each row.
You say the "The size of the array is definite" aren't you actually saying that it is limited to 18 eg one value 1-18 being the max and as you split it out into separate groups with gaps it reduces.
On a line by line basis this is fine if it is in the last column but what do you want it to do if it is not the last column and it has to push columns out as the number of values changes.

eg Col1 1 2 3 13 15 16 17 Col9
Col1 4 6 9 18 Col6
Col1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 Col20

 

[ChewCS]

Dim inArray() As Variant

ref = "A1:B10"     'defined size
path = Application.ThisWorkbook.path & "\"
file = "test.xlsx"
sheet = "Sheet1"
arg = "'" & path & "[" & file & "]" & sheet & "'!" & ref

With Range(ref)
    .FormulaArray = "=" & arg
    .Value = .Value
End With

inArray = Range(ref)

This is how I would read in an input file, which looks like this:

Day	Time	Duration	Module	Weeks
Mon	8:30	1:30	ME1401	1-6,11,13,15,17
Tue	9:30	2:00	ME2101	2,4,6,12,14,16,18

It is basically a timetable data file meant for identifying clashes of time slots. And I wish to eventually assign all the data under a 2D array which I can run my search with. However, I cannot search with the Weeks column in such a string format. Therefore I would like to convert to something like this:

Day	Time	Duration	Module	Week 1	Week 2....	Week 11	Week 12	Week 13	Week 14...
Mon	8:30	1:30	ME1401	1	1	1	0	1	0
Tue	9:30	2:00	ME2101	0	1	0	1	0	1

My original question was for the conversion part so that I can eventually end up with this 2D array.

 

[kvsrinivasamurthy]

Day​	Time​	Duration​	Module​	Weeks​	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21
Mon​	08:30​	01:30​	ME1401​	1-6,11,13,15,17​	1	1	1	1	1	1	0	0	0	0	1	0	1	0	1	0	1	0	0	0	0
Tue​	09:30​	02:00​	ME2101​	2,4,6,12,14,16,18​	0	1	0	1	0	1	0	0	0	0	0	1	0	1	0	1	0	1	0	0	0

Note the change in Row1. Only week numbers are entered.
UDF in F2 then copied to full range

=MarkWeekNum($E2,F$1)

code for UDF 'MarkWeekNum'

Function MarkWeekNum(S As String, k As Integer) As Long
Dim M, N, P
Dim T As Long, Ta As Long
P = Split(Replace(S, "-", ","), ",")

If Val(P(0)) > k Then GoTo Line1

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)))
    If k < Ta Then
    GoTo Line1
    ElseIf k = Ta Then
    MarkWeekNum = 1: GoTo Line1
    End If
    Next Ta
Else
If k < Val(M(T)) Then
GoTo Line1
ElseIf k = Val(M(T)) Then
MarkWeekNum = 1: GoTo Line1
End If
End If
Next T
Line1:
End Function

How to Use UDF code:
In the developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the VB window.
Now UDF is available in Function List
Save file as .xlsm

 

[ChewCS]

Day​	Time​	Duration​	Module​	​ Weeks​	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21
Mon​	08:30​	01:30​	ME1401​	1-6,11,13,15,17​	1	1	1	1	1	1	0	0	0	0	1	0	1	0	1	0	1	0	0	0	0
Tue​	09:30​	02:00​	ME2101​	2,4,6,12,14,16,18​	0	1	0	1	0	1	0	0	0	0	0	1	0	1	0	1	0	1	0	0	0

Note the change in Row1. Only week numbers are entered.
UDF in F2 then copied to full range

=MarkWeekNum($E2,F$1)

code for UDF 'MarkWeekNum'

Function MarkWeekNum(S As String, k As Integer) As Long
Dim M, N, P
Dim T As Long, Ta As Long
P = Split(Replace(S, "-", ","), ",")

If Val(P(0)) > k Then GoTo Line1

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)))
    If k < Ta Then
    GoTo Line1
    ElseIf k = Ta Then
    MarkWeekNum = 1: GoTo Line1
    End If
    Next Ta
Else
If k < Val(M(T)) Then
GoTo Line1
ElseIf k = Val(M(T)) Then
MarkWeekNum = 1: GoTo Line1
End If
End If
Next T
Line1:
End Function

How to Use UDF code:
In the developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the VB window.
Now UDF is available in Function List
Save file as .xlsm

Thanks for this udf, it works
However, I am thinking of the entire process and the additional steps required to implement this udf.

As of now, my original code lies in a Command Button. Upon clicking, the code reads the input file, does the conversion and everything will be in a 2D array, which I will write the rest of my decision scripts to report time slot clash or no clash, etc.

With this udf, I will have to take the input file, copy the data onto this .xlsm, run the udf, and then invoke the Command Button to run the decision scripts. This will eventually be run by a user who will not be familiar with the vba scripts, and I'm trying to reduce the effort required.

 

[Alex Blakenburg]

Are the headings already in your input file ? Can we assume there will be 18 weeks and set up the headings for 18 weeks in the code ?

 

[ChewCS]

Are the headings already in your input file ? Can we assume there will be 18 weeks and set up the headings for 18 weeks in the code ?

The headings are not in the input file, but it's no big issue. What I really want is the 2D array with rows of data such as day, time, duration, etc. I can read those from the input file, but I will convert the "Weeks" string into 18 columns (yes 18 is fixed) of 1 or 0 and append them to the end of the 2D array.