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 Office 365.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Finding and replace dates contained within other text in a cell can be a bit tricky. This tip looks at some approaches to ...
Discover MoreWant to use Find and Replace to change the formatting of a cell's contents? You would be out of luck; Excel won't let you ...
Discover MoreIf you need to replace information that may appear in cells, comments, and text boxes, your best bet is to use a macro. ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2021 Sharon Parq Associates, Inc.
Comments