Limitations On Finding Characters

Written by Allen Wyatt (last updated April 11, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


1

Harold wonders if there is a limitation of characters in a cell beyond which "Find" will not find a string. He has a lot of text in one cell (22,500 characters) where the string MIMO is near the end, and "Find and Replace" says it's not there. The reason he knew it was there was because he was testing an array formula using the SEARCH function, and it said MIMO was there. Harold thought he had an error in his formula, but he seemed to find this Excel limitation instead.

I wasn't able to find any limitation noted from any Microsoft source, but in testing there is definitely a limitation. I created a string that was just as long as Harold's, and Find and Replace would not find the characters near the end. That got me to wondering where the actual limit occurred.

It appears that the limit is at 8,192 characters. If the string is exactly 8,192 characters long and the last four characters are "MIMO," then Find and Replace can recognize it with no problem. Add one more character to the string (so it is 8,193 characters long), and if "MIMO" is still at the end, Find and Replace won't find it. If the characters are earlier in the string—before the 8,192-character boundary—then Find and Replace finds it, just as you would expect.

The bottom line is that if you are working with very long text strings in a worksheet, you cannot rely on Find and Replace to locate desired text. If you absolutely must work with long text strings, you might consider developing your own VBA routines to do the searching, as VBA doesn't have this built-in limitation.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9237) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Using the ABS Function

Need to find the absolute value of a number? That's where the ABS function comes into play.

Discover More

Putting a Different Date in a Header

Today's date is easy to add to a header, but what if you want to add a date that is adjusted in some manner? Adding ...

Discover More

Creating Individual Workbooks

Workbooks can contain many worksheets. If you want to pull a workbook apart and create a whole series of workbooks based ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Wildcards in 'Replace With' Text

When doing searches in Excel, you can use wildcard characters in the specification of what you are searching. However, ...

Discover More

Searching for Leading Apostrophes

Take a look at the Formula bar when you select a cell that contains text, and you may see an apostrophe at the beginning ...

Discover More

Making All Occurrences Bold

Want to make instances of a given word or phrase bold throughout a worksheet? Here's a way you can make the change quickly.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two less than 9?

2021-10-12 08:39:45

mh

Thanks for clarifying this issue, which has been bugging us from time to time. This seems rather arbitrary by Microsoft, especially given that the maximum cell character length is at 32,767 characters. Of course, it isn't documented anywhere officially.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.