Search 500 characters within 4000 characters

ssuastegui 

If you are willing and able to: 1) use an additional worksheet column for the result of the search, and 2) create and use a user-created VBA function, there is an easy solution.

Let's say the search text is in column B, and the long text (text to be searched) is in column E.  Column G will contain the result of the search: 0 if the search text is not found, or a positive number indicating where the text was found.

First, save the workbook as a macro-enabled workbook (*.xlsm).

Then press Alt+F8 to get the dialog to access VBA procedures, enter SearchLong as the "Macro name", make sure "This Workbook" is selected in the "Macros in" dropdown list, and press the Create button.  The editor window appears with this basic code:

Sub SearchLong()

End Sub

A Sub[routine] performs an action, but does not return anything.  You will change that Sub to a Function, which does return a value (or object); replace those three lines with:

Public Function SearchLong(ByVal SearchTerm As String, ByVal LongText As String _
        , Optional ByVal StartChar As Long = 1) As Long

    Dim in4ReturnValue     As Long
    
    '----   Do a case-insensitive search:
    in4ReturnValue = InStr(StartChar, LongText, SearchTerm, vbTextCompare)
    
    '----   Return the result:
    SearchLong = in4ReturnValue
    Exit Function

End Function

(I wrote the function to use the same arguments as the built-in SEARCH function, in the same order.)

Save your work again.  Now you can use that function in cells in your worksheet.  So, e.g., you can use this formula in cell G2:

=SearchLong( B2, E2 )

(Because this is a Public function, you will see SearchLong appear in the list of available functions as you type.)

And of course, feel free to choose a different name for the function.  As you become familiar with VBA, you will learn that you could even add an argument (and slightly change the code) to allow case-sensitive searches.