Forum Discussion

ssuastegui's avatar
ssuastegui
Copper Contributor
Oct 11, 2022
Solved

Search 500 characters within 4000 characters

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), VLOOK...
excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Oct 11, 2022

    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

mtarler's avatar
mtarler
Silver Contributor
Oct 11, 2022

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

  • ssuastegui's avatar
    ssuastegui
    Copper Contributor
    Oct 11, 2022
    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.