Excel - String Replace - Formula too Long

Posted on 2003-02-18
Medium Priority
Last Modified: 2012-05-04
I am trying to use string replace function (Ctrl-H) in a excel sheet. But for certain fields which exceeds say 1000 characters it's not able to do and pops a message box "Formula too long". How will I over come the same, can I change some settings so that I don't receive the same error message.

Question by:RaghunathR
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 22

Expert Comment

ID: 7979493
Just my suggestion:

Copy all cells to Word doc.
Do Ctrl+H, etc.

Click anywhere in table, Hit Table-Select table.


Paste back into Excel.

Hope this helps!
LVL 16

Expert Comment

ID: 7979519
I've just tried to find the limit by dichotomy: the limit is 911. After that (>911) it doesn't work anymore: as you said , it says "Formula too long".
I don't know if it is machine dependent, or if it is software dependent. And i haven't found anything about it in the limits listed in the online help.

I don't know about any existing settings to change to overcome this situation.
Maybe a macro could do the trick. Are these long fields only text? or also numbers?
LVL 16

Expert Comment

ID: 7979527
Nice trick Dreamboat... works very well.

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 23

Expert Comment

ID: 7979720
Here is something else that should work, or at least it did for me.....

If you put this in a module and run it it will replace everything on the sheet regardless of how long the cell value is:


Sub ReplaceStr()
    Dim OriVal As String
    Dim RepRange As Range
    Dim RepWhat As String
    Dim RepWith As String
    Dim TempStr As String
    Dim RepLen As Integer
    RepWhat = InputBox("Find what?")
    RepWith = InputBox("Replace with what")
        RepLen = Len(RepWhat)

    Set RepRange = ActiveSheet.UsedRange
    For Each cell In RepRange
        OriVal = cell.Value
        Do Until InStr(OriVal, RepWhat) = 0
            TempStr = TempStr & Left(OriVal, InStr(OriVal, RepWhat) - 1) & RepWith
            OriVal = Right(OriVal, Len(OriVal) - ((InStr(OriVal, RepWhat) - 1) + RepLen))
       cell.Value = TempStr & OriVal
  TempStr = ""
  OriVal = ""
    Next cell

End Sub


This could be modified of course to only include cells that are selected or use a form instead of 2 input boxes which would make it a little nicer.  But that part is easy.  If you need help putting this in a module, let me know and I'll tell you how to do that.


LVL 23

Expert Comment

ID: 7981285
Hi RaghunathR

One more thing:  If you put that code in personal.xls, it will available to any workbook you have open, not just the one that you are working with.

LVL 23

Accepted Solution

ahammar earned 400 total points
ID: 7981299
Hi again RaghunathR,

I lied.... One more thing..

You can also assign that to a shortcut key such as Ctrl + R for example (or whatever you prefer) so you don't have to run the macro with your mouse evertime.  Maybe you know all this already, but in case you don't.....


Author Comment

ID: 7997313
This works thanks ahammar for the solution

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question