How to check for a variable number within a string

Question

Good Morning,

I am attempting to create VBA code that will identify if a variable value (number) is found within a string. The string can vary in lenght and can contain 1 or more numbers that are sepearted by a , and a space. I have attempted to use the InStr method but unfortunately if my value is 1 and the string contains a 17 it comes back as true. How can I make it so that would return false since 1 is not equal to 17.

Below is my current code:

'FRB_Code and FRB_Array are variable values within my code but for 
'the purpose of this question I have assigned them values.
    FRB_Array = "10, 17, 21" 
    FRB_Code = 1 'ce.Value
    If InStr(FRB_Array, FRB_Code) Then
    MsgBox "Found"
    Else
    MsgBox "Not Found"
    ce.Delete Shift:=xlUp
    End If

    Next ce
    End If

So the end result should be that the FRB_Code was not found in the FRB_Array and there for the cell was deleted.

Thank you for you help.

does your values in string always separated wuth commas like this: "10, 17, 21"? — Dmitry Pavliv
– Dmitry Pavliv, Commented Mar 6, 2014 at 15:37
The String "1" does contains in the array (i.e. "1"7, 2"1"), hence you can try define the element in the array by splitting with comma, and do a comparsion — Alex
– Alex, Commented Mar 6, 2014 at 15:40

Kapol · Accepted Answer · 2014-03-06 15:57:31Z

1

You can use an array for that.

Sub FindValue()
    Dim sMyString As String
    Dim sToFind As String
    Dim Arr
    Dim i As Long
    Dim bFound As Boolean

    sMyString = "10, 17, 21"
    Arr = Split(sMyString, ", ")
    sToFind = "17"

    For i = 0 To UBound(Arr)
        If Arr(i) = sToFind Then
            MsgBox "Found"
            bFound = True
            Exit For
        End If
    Next i

    If Not bFound Then MsgBox "Not found"
End Sub

answered Mar 6, 2014 at 15:57

Kapol

6,4834 gold badges26 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gary's Student · Accepted Answer · 2014-03-06 15:43:11Z

1

Problem is that "1" will "instring" to "1", "217","871", etc. Better to pre-pad and post-pad with spaces:

Sub NumberInString()
    BigString = " 1 23 54 79 "
    LittleString = " 23 "
    MsgBox InStr(1, BigString, LittleString)
End Sub

answered Mar 6, 2014 at 15:43

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Comments

Aidan · Accepted Answer · 2014-03-06 15:58:23Z

1

InStr is not really appropriate here because you are comparing numbers rather than strings. To do what you want split the string into pieces and cycle through the returned array checking each item. Val is used to convert each item in the array to an integer.

bFound = False

FRB_Array = "10, 17, 21"
FRB_Code = 17

ar = Split(FRB_Array, ",")

For i = 0 To UBound(ar)

    If FRB_Code = Val(ar(i)) Then
        bFound = True
    End If
Next i

If bFound Then
    MsgBox ("found")
Else
    MsgBox ("not found")
End If

answered Mar 6, 2014 at 15:58

Aidan

1,8101 gold badge16 silver badges23 bronze badges

Comments

Arbra · Accepted Answer · 2014-03-06 16:12:41Z

1

You can use REGEX to determine the match.

http://msdn.microsoft.com/en-us/library/twcw2f1c(v=vs.110).aspx

the regex expression would be "1[^\d]|1$" and you would replace 1 with your FB_Code value.

The expression has an or(|) to handle the last number in the array.

edited Mar 6, 2014 at 16:12

answered Mar 6, 2014 at 16:00

Arbra

112 bronze badges

Collectives™ on Stack Overflow

How to check for a variable number within a string

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related