Search 500 characters within 4000 characters

[ssuastegui]

Hello,

My goal is to search if my cell with ~500 characters is exactly found (case insensitive) in another cell containing ~4000 characters. 

I have tried ISNUMBER(SEARCH), IF(COUNTIF), VLOOKUP and XMATCH making use of the wildcard functions. All of these worked when I tested them with shorter strings, but they break when I use the ~500 character search reference. Most of the results end in #VALUE!

I think I am running into the issue of the argument limit despite trying concatenations. I am breaking my ~500 character cell down, then searching the concatenation of those split cells.

Please advise, thank you.

[#2 · Accepted solution]

yes it appears you are hitting the 255 character limit on the search term. Try this (good up to 510 characters but could be expanded further but would get a little more messy):
=SEARCH(MID(A1,256,255),MID(A2,SEARCH(LEFT(A1,255),A2)+255,255))=1

edit: btw this will fail if the 1st 255 characters match but the next 255 do NOT match but there IS a complete match starting AFTER the location of that first 255 character match

[#3]

Thank you very much. Some of my cells of course go up to ~520 characters, just outside the limit. I think I have another solution in mind where I can search the broken up strings of 200 characters one by one I will keep your solution bookmarked for future reference.

[#4]

@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.