Limitations On Finding Characters

Some things in Excel founder when the length of a string of information reaches values like 32767. Others founder at values in the 6,000 range and the 8,000 range.

I always figured that was a limit of the function, older ones, perhaps written with more limited resources, making, say, 8-bit variables instead of whatever the system can handle. Maybe I was right too.

But, perhaps functions rely also on an underlying "rendering" engine that presents them with whatever, and that's all they get hence their limits on things like length.

If you do as Mike says, but dial the length down by reducing the 700 to whatever (100 puts the length at 4404 and works properly where 200 does not. Sound like a 6,000-ish issue?) and Find begins to work as expected.

No function/formula/Named Range/etc. is involved, just a clear limit on how far into the string Find can go. Since MS's general approach is something that overloads just fails, too bad ("NO! Too bad for you! And I won't tell you why!"), and presents an error ("graceful" fail), one suspects it is not that Find overloads and fails "ultra-gracefully" - just stopping on one cell's content with no warning that it did so, but presenting other results (not stopping either, put a good one after a bad one and it finds the good one, so not stopping at first failure and missing 2,000 good ones that follow).

The limit I find here is length 8192 - which should be familiar to anyone who has ever looked at powers of 2 past 4096. (The 32767 thing is 1 less tha 32768 which is four times this length. I never did see offhand where the 6,000 range limits arise from.)

Find either stops at that length or is never presented anything longer than that length to start with.

So... there IS a limit on Find. However it arises.

If using MATCH, the limitation on the maximum number of characters in a text box being searched in 255. Beyond this you will an #N/A result even if the text string is found in the cell.

This is annoying - now to find a work around

Thank you Allen, the only post I have found that could help me with this annoying problem.

The limit for Excel's depth of string search in a cell using the UIs Find & Replace is 8192 characters. The solution is to use VBA, which will search to any depth.

This tip is not correct. Here's how to replicate Harold's problem:

1) Type "The quick brown fox jumped over the lazy dog" in cell A1
2) In cell A2 type =REPT(A1,700)&"asdf"
3) Copy A2 and choose "Paste Special -- Values" in A3
4) Ctrl+F search for "the" (Within Sheet, Search By Rows, Look in Formulas). Find will show you A1 and A3. This is the expected behavior.
5) Do the same search as (4), but search for "asdf". To be consistent, we should see A2 and A3, but Find will ONLY show you A2. This is the error Harold described.

While A3 is not a formula, we have clearly demonstrated that text values are searched as formulas when the result is early enough in a cell. If you know that you should have a result and the text is very large, change Look in to Values and Find will return A2 and A3.