Some time we need to create an excel document from an existing one.
We would then replace some of the values with new values…
For that, we use “find and replace” function of ms excel that time, that saves our time and does the job accurately…
We get problem when there are a couple of cells which have large amount of text in them and if the text contains more then 1024 characters….
MS Excel will show error message saying “formula too long” and won’t replace anything.
- We need to copy the whole text in notepad,
- Do a “find and replace all” there.
- Copy the replaced text back to excel sheet in that particular cell.
only for two – three cells, one would do that.
But what to do when your excel sheet has n number of cells like that ?
I don’t think any one would like to do the same repeat laborious work ….
I too don’t like .. so I searched on google.. and found out below article:
They write on their website : “Flexfind eases searching throughout an entire workbook. Enter a string to search for and get a list of all found locations. Click on an entry to go there. Select workbook, sheet or range searching. Replace with another string in the found items you select. At your wish, get a confirmation for each occurrence found (also within one cell). MAC enabled (thanks to Matthew Henson). Ever needed to replace a company name in the title of 25 charts? Or replace just the third occurrence of a cell address in some cells’ formulas? Or change a part of the printheader of 6 sheets? Now there is Flexfind, which enables Search and Replace in all these items and more!!!”
They have done a wonderful job, to overcome above illustrated problem…
I am going to test their code with sample data… I am sure it will work.
Sometime you also might get to work with such document and get the same error.. What will happen at that time?
- you would also search on Google the way I did.
- you would come to my weblog (this article)
- you will get above link and get the solution