હિમાંશુ પરભુભાઇ મિસ્ત્રી | Himanshu Mistry

March 29, 2010

“formula too long” error message in Excel Find and Replace

Filed under: Uncategorized — Himanshubhai Mistry @ 4:03 pm

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.

Steps:

  1. We need to copy the whole text in notepad,
  2. Do a “find and replace all” there.
  3. 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:

Search And Replace Made Easy

source: http://www.jkp-ads.com/officemarketplaceff-en.asp

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
About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: