Obtaining and Comparing Integers in a String

Question

I'm trying to take a string input with numbers, ex. (1, 5, 7, 10, 16, 53, 2) and analyze it to see if any one value is >=some integer value.

I know that there's a sort of parseInt function but I don't think this would work for the format and content of the string, as it contains multiple numbers separated by commas.
I know that there's also a function (InStr) to find a certain value in a string, but this doesn't fit my needs as it would require writing cases from 1 to 1000+.

Would transforming the string into an integer array work? Is it a complicated process for VBA?

pseudo code

Dim element As Range
Dim OrgListcolE As Range

For each element in OrgListcolE
    If there exists a value in the element.Value >=16 Then
        ...
    ElseIf there exists a value in element.Value >=51
        ...
    ElseIf there exists a value in element.Value >=251
        ...
    ElseIf there exists a value in element.Value >=1001
        ...
    End If

Use `Split` to split the string into an array, then convert array elements to `Long`. — BigBen, Jul 14 '21 at 18:49
Your post says numbers are in a string. The code is indicating the numbers are in a range `OrgListcolE`. Which is it? — Siddharth Rout, Jul 14 '21 at 18:58
The numbers are in a string format, but they are contained within a cell. — CBlue, Jul 15 '21 at 15:38
@CBlue Posted a solution via `FilterXML()` function which allows to return list elements immediately as numeric values based on given filter conditions. As you got several answers, feel free to accept any preferred post by ticking the green checkmark if helpful - c.f. [Someone answers](https://stackoverflow.com/help/someone-answers) — T.M., Jul 15 '21 at 15:44

score 1 · Accepted Answer · answered Jul 14 '21 at 18:58

1

Loop and check:

Sub topcheck()
    Dim s As String, limt As Long
    s = "1, 5, 7, 10, 16, 53, 2"
    arr = Split(s, ", ")
    limt = 50
    For Each a In arr
        If CLng(a) > limt Then
            MsgBox "a value in the string exceeds " & limt
            Exit Sub
        End If
    Next a
    MsgBox "no value in the array exceeds " & limt
End Sub

answered Jul 14 '21 at 18:58

Gary's Student

95,722
10
59
99

Thank you! Will try this out. – CBlue Jul 15 '21 at 15:39
Straight forward solution :+) - fyi maybe you are interested in alternative `FilterXML` approach. – T.M. Jul 26 '21 at 17:24

score 1 · Answer 2 · answered Jul 14 '21 at 19:22

1

Option with Evaluate():

Sub test1()
    numbers = "1, 5, 7, 10, 16, 53, 2"
    max_num = Evaluate("Max(" & numbers & ")")
    Debug.Print IIf(max_num > 20, "", "no ") & "any number exceed limit"
End Sub

answered Jul 14 '21 at 19:22

Алексей Р

7,507
2
7
18

Thank you! Will try this out. – CBlue Jul 15 '21 at 15:39
You are checking if the highest input number (e.g. 53) exceeds a given limit. - Doesn't OP want the opposite asking *"to take a string input with numbers, ex. (1, 5, 7, 10, 16, 53, 2) and analyze it to see if any one value is >=some integer value"*? – T.M. Jul 15 '21 at 16:54

score 0 · Answer 3 · answered Jul 15 '21 at 15:37

FilterXML() approach

The FilterXML() function, available since vers. 2013+, allows to return filtered numbers from a (string) list which has been transformed to well-formed xml content.

XML content and individual limits are passed as arguments to help function chk which assigns all findings to a "flat" 1-dim array as function result. FilterXML() applies a so called XPath expression on the xml content based on the given limits, thus forming a string via "//s[.>=" & limit & "]".

Example call

Sub TestNumList()
'1) definitions
    Dim myString As String
    myString = "1,5,7,10,16,53,2"   ' comma separated list (here without spaces!)

    Dim xml As String               ' build xml content string
    xml = "<t><s>" & Replace(myString, ",", "</s><s>") & "</s></t>"

    Dim limits()                    ' define limits
    limits = Array(16, 51, 251, 1001)

'2) check results
    Dim i As Long, elems As Variant
    For i = LBound(limits) To UBound(limits)
        '~~~~~~~~~~~~~~~~~~~~~~~~~~
         elems = chk(xml, limits(i))              ' << help function chk
        '~~~~~~~~~~~~~~~~~~~~~~~~~~
        Debug.Print "Limit " & limits(i) & ":", UBound(elems) & " elem(s) found.", Join(elems, ",")
    Next

End Sub

Display in VB Editor's immediate window:

    Limit 16:     2 elem(s) found.            16,53
    Limit 51:     1 elem(s) found.            53
    Limit 251:    0 elem(s) found.            
    Limit 1001:   0 elem(s) found.

Help function chk()

Function chk(xml As String, ByVal limit As Long)
'Purpose: assign listed numbers >= individual limit to 1-dim array
'Author:  https://stackoverflow.com/users/6460297/t-m
'Date:    2021-07-15
'a) filter elements greater or equal limit
    Dim tmp
    tmp = Application.FilterXML(xml, "//s[.>=" & limit & "]")
'b) return elements as 1-based "flat" 1-dim array
    If TypeName(tmp) = "Variant()" Then  ' found several elements
        chk = Application.Transpose(tmp) ' (change 1-based 2-dim to 1-dim array)
    ElseIf IsError(tmp) Then             ' found no elements at all
        chk = Array(Empty)               ' (empty)
    Else                                 ' found single element only
        ReDim tmp2(1 To 1)               ' provide for one element
        tmp2(1) = tmp                    ' (numeric value)
        chk = tmp2
    End If
End Function

Recommended link to @JvdV 's nearly encyclopaedic post treating FilterXML()

Extract substrings from string using FilterXML()

Obtaining and Comparing Integers in a String

3 Answers3