EXCEL VBA: Find first instance of String

If have a list of files in cell A1. What is the fastest VBA code I use to find the full name of the first ".bat". See example.


Billy Joel
Ray Charles
Carl Junior

In the above example the answer would be "Data.bat" since it is the first .bat file.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try this:

Function findText(ByVal v As String, ByVal find As String) As String
    On Error GoTo EH
    Dim tmpArr() As String
    tmpArr = Split(v, Chr(10), , vbTextCompare)
    For i = 0 To UBound(tmpArr)
        If InStr(1, tmpArr(i), find, vbTextCompare) > 0 Then
            findText = tmpArr(i)
            Exit Function
        End If
    Next i
    Exit Function
    findText = ""
End Function

Open in new window

then use:

=findText(A1, ".bat")

Open in new window

Bill PrewIT / Software Engineering ConsultantCommented:
Should be able to do something like this, adjust options to .Find() as needed.

    Set c = ActiveSheet.Cells.Find(What:="data.bat", After:=ActiveSheet.Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    Debug.Print c.Address & " = " & c.Value

Open in new window

pls try
Function findMyText(ByVal v As String, ByVal find As String) As String
    findMyText = ""
    Dim tmpArr() As String
    tmpArr = Split(v, Chr(10), , vbTextCompare)
    On Error Resume Next
    findMyText = Filter(tmpArr, find)(0)
End Function

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

If you know that the ".bat" string will always be a particular case (for instance: upper, lower, proper), then you can speed this up a bit by changing vbTextCompare to vbBinaryCompare
Function Q_29079803()
    Q_29079803 = Filter(Split(Cells(1, 1).Value, vbLf), ".bat", True, vbTextCompare)(0)
End Function

Open in new window

Note: this code/solution assumes that there will be at least one line containint a ".bat" string.
You can easily extend this code to any multi-line cell, passing the cell (range) as a parameter.  You can also extend it to look for strings other than ".bat" like the Ryan Chong and RGONZO solutions above.
Function Q_29079803(parmCell, ByVal parmString As String)
    Q_29079803 = Filter(Split(parmCell.Value, vbLf), parmString, True, vbTextCompare)(0)
End Function

Open in new window

ouestqueAuthor Commented:
Thank you everyone! Great Solutions!
I couldn't stand the lack of resilience in my initial answer.
Function Q_29079803()
    Const cBat As String = ".bat"
    Dim strCellText As String

    strCellText = Cells(1, 1).Value
    If InStr(1, strCellText, cBat, vbTextCompare) <> 0 Then
        Q_29079803 = Filter(Split(strCellText, vbLf), cBat, True, vbTextCompare)(0)
        Q_29079803 = vbNullstring
    End If
End Function

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.