Find value in text string of numbers using VBA or formula

Question

I need to find a value in text string of numbers where a "." will represent a range between the values and a "," is a break between two values.

Example: I need to determine if 116 is in the text string

[000.117,119.131,137,140.999]

Yes, 116 would be in the list while 136 would not.

There will be over one thousand lines that I will have to test and three different values on each line (region, department and account). I just need help starting with either a formula or VBA code to test if value is in text string. My only option is to brute force the fifty plus text strings into a list of numbers but the list of text strings could change from month to month and it will take several hours to covert the fifty plus text strings into a list of numbers. Thank you.

Does it have to be 116 as a unique number? If you have [000.117, 119.131, 1165.123, 114], is "116" considered there? Also, does 116 need to be before the decimal, after it, or does it not matter? You could also split the text into columns, with a comma delimiter, and use LEFT() with SEARCH() perhaps...What have you tried so far? Also, what does "Yes, 116 would be in the list while 136 would not." mean? I don't see 116 anywhere in your example, nor 136...what's 136 have to do with anything? — BruceWayne
– BruceWayne, Commented May 15, 2017 at 22:35
@BruceWayne I believe he means 0,1,2,3...,117,119,120,...131,1165...etc are in the list. They are lists of ranges so your 1165.123 doesn't make sense. His example is the list of numbers 0 to 999 excluding: 118, 132 to 136, 138 and 139 — Nick is tired
– Nick is tired, Commented May 15, 2017 at 22:36
How about splitting the string with , as delimiter, and afterwards splitting again each element of the resulting array with . as deliminter. Inside this portion of the loop check if your value (116) is bigger than first element and less than the second. — CMArg
– CMArg, Commented May 15, 2017 at 22:51
Yes, Nick A comments are correct. I'm looking for a list of whole numbers between 0 to 999 excluding 118, 132 to 136, 138 and 139. I have 50 list of ranges that I will be searching values against with each list having a different set of numbers. They will be in the format provided in the example. Thanks. — Hedrick24
– Hedrick24, Commented May 15, 2017 at 22:52
@Hedrick24: BruceWayne answer seems to perform what you were asking for. If it fits your needs, please mark as accepted. Otherwise, it would be polite to comment on it. — CMArg
– CMArg, Commented May 16, 2017 at 9:47

BruceWayne · Accepted Answer · 2017-05-15 23:09:53Z

Not sure how your data is laid out, but this should get you started.

It's a function that takes two arguments, one is the string to search with, and the other is what you're looking for.

Function checkRange(str As String, findStr As Long) As String
Dim strArray() As String
strArray() = Split(str, ",")

Dim i       As Long
Dim rng()   As String
Dim bottom As Long, top As Long
For i = LBound(strArray) To UBound(strArray)
    Debug.Print strArray(i)
    If InStr(1, strArray(i), ".") > 0 Then
        ' there is a decimal
        rng = Split(strArray(i), ".")
        bottom = rng(0)
        top = rng(1)
        If bottom <= findStr And top >= findStr Then
            'MsgBox (findStr & " is in the range " & strArray(i))
            checkRange = findStr & " is in the range " & strArray(i)
            Exit For
        Else
            checkRange = findStr & " is NOT in any range"
        End If
    End If
    If strArray(i) = findStr Then
        checkRange = findStr & " is in the range " & strArray(i)
        Exit For
    End If
Next i

End Function

You can call it like:

checkRange "000.117,119.131,137,140.999", "116"

or in Excel cell:

Collectives™ on Stack Overflow

Find value in text string of numbers using VBA or formula

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related