Forum Discussion

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

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

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.