How to find an element in an array through a specific character in the element?

Question

I have list of elements in array. For example:

arr = ("numbers", "matriculation", "123", "444.909", "465", "897.907")

From the array, I would like to find the elements (index/position) that contains ".90" . When I tried, it returned empty/nonexistant.

From what I found from other websites, you can only find specific elements when it is exactly the same. For example:

arr = ("numbers", "matriculation", "123", "444.909", "465", "897.907", "123")
If  arr("123") = "123" 
strMsg = "True"

Answer 1

Just loop through the elements of your array and compare them using the Like operator:

Option Explicit

Public Sub Example()
    ' define an example array with data
    Dim DataArray() As Variant
    DataArray = Array("numbers", "matriculation", "123", "444.909", "465", "897.907")
    
    Dim Element As Variant
    For Each Element In DataArray  ' loop through all elements
        If Element Like "*.90*" Then  ' do a comparison with placeholders
            Debug.Print Element  ' output in immediate window what matched
        End If
    Next Element
End Sub

This will output

444.909
897.907

in the immediate window.

---

Alternatively filter your array to get an array with the filtered values only:

Public Sub Example2()
    ' define an example array with data
    Dim DataArray() As Variant
    DataArray = Array("numbers", "matriculation", "123", "444.909", "465", "897.907")
    
    Dim FilteredArray As Variant
    FilteredArray = Filter(DataArray, ".90")  ' filter the array
    
    ' filtered array now contains only "444.909" and "897.907"
    Debug.Print Join(FilteredArray, ", ")
End Sub

Note the filter is case sensitive. If you don't want that use Filter(DataArray, ".90", , vbTextCompare) instead.

Answer 2

In order to complete @PEH 's valid solutions an approach via FilterXML() (available since vers. 2013); by default it returns a vertical array (procedure chkResults provides for special cases of non-findings or single values) with the additional benefit that it recognizes numeric values as such .

Note that the results returned by the following Filt() function always return a 2-dim array. An example call is listed eventually.

Function Filt(flatArray, ByVal srch As String)
'a) define wellformed xml string
    Dim xml As String
    xml = "<ii><i>" & Join(flatArray, "</i><i>") & "</i></ii>"
'b) define XPath search string
    Dim xPth As String
    xPth = "//i[contains(.,'" & srch & "')]"
'c) filter xml and return findings
    Filt = Application.FilterXML(xml, xPth)
    chkResults Filt ' check for special cases
End Function

Help procedure chkResults

Sub chkResults(ByRef FilterXMLResults)
'Purp:  provide for special cases zero or 1 finding
'Note:  by default FilterXML returns a 2-dim array for more findings,
'       otherwise results have to be redimmed
    Select Case VarType(FilterXMLResults)
        Case Is >= vbArray: Exit Sub    ' 2 or more findings
        Case vbError                    ' 0 findings
            ReDim xtra(0 To 0, 1 To 1)
        Case Else                       ' 1 finding only
            ReDim xtra(1 To 1, 1 To 1)
            xtra(LBound(xtra), 1) = FilterXMLResults
    End Select
    FilterXMLResults = xtra ' return special cases as 2-dim array, too
End Sub

---

Example call

Sub testFilt()
Const srch As String = ".90"     ' ".90x"
'1. define data
    Dim data() As Variant
    data = Array("numbers", "matriculation", "123", "444.909", "465", "897.907")
'2. get results
    Dim results As Variant
    results = Filt(data, srch)      ' << help function Filt() via FilterXML
'3. show results
    Debug.Print UBound(results) & " item(s) found:"
    Dim i As Long
    For i = LBound(results) To UBound(results)
        Debug.Print i, results(i, 1)
    Next i
End Sub

Example results in VB Editor's immediate window

'2 item(s) found:
1             444.909
2             897.907

---

Extended version //Edit as of 2022-09-27

You might want to apply Filt() similar to VBA's Filter() function (VBA.Strings.Filter) to include the possibilities of

• negative filtering via additional boolean argument Include:=False as well as
• case insensitive filtering via boolean argument TxtComp:=True

Caveat using FilterXML

Note that the XPath search expressions seem to require the xmldom functions themselves - like translate()etc. - to be written in lower cases; so a FilterXML xpath starting with e.g. //i[contains(Translate(., ...),'xyz')] would fail.

Function Filt(flatArray, ByVal srch As String, _
    Optional Include As Boolean = True, _
    Optional TxtComp As Boolean = False)
'a) define wellformed xml string
    Dim xml As String
    xml = "<ii><i>" & Join(flatArray, "</i><i>") & "</i></ii>"
'b) define XPath search string
    Dim xPth As String
    If TxtComp Then srch = LCase(srch)
    If Include Then
        xPth = "//i[contains(.,'" & srch & "')]"
    Else
        xPth = "//i[not(contains(.,'" & srch & "'))]"
    End If
    If TxtComp Then ' extend above XPath by xmldom translate function
        Dim ABC As String: ABC = UCase(srch)
        Dim translate As String
        translate = "translate(.,'" & ABC & "','" & LCase(ABC) & "')"
        xPth = Replace(xPth, "(.,", "(" & translate & ",")
    End If
'c) filter xml and return findings
    Filt = Application.FilterXML(xml, xPth)
    chkResults Filt ' check for special cases
End Function

Related helpful links

• @JvdV 's nearly encyclopaedic overview Excel - Extract substrings from string using FilterXML
• Tips to case sensitivity Allow case insensitive search via FilterXML