• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2932
  • Last Modified:

Excel - String Replace - Formula too Long

Hi,
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.

Thanks
Raghunath.R
0
RaghunathR
Asked:
RaghunathR
1 Solution
 
DreamboatCommented:
Just my suggestion:

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

Click anywhere in table, Hit Table-Select table.

Copy.

Paste back into Excel.

Hope this helps!
0
 
sebastienmCommented:
RaghunathR,
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?
 
Regards,
Sebastien
0
 
sebastienmCommented:
Nice trick Dreamboat... works very well.

Sebastien
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ahammarCommented:
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))
        Loop
       
       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.

Cheers!
ahammar


0
 
ahammarCommented:
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.

Cheers!
ahammar
0
 
ahammarCommented:
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.....

Cheers!
ahammar
0
 
RaghunathRAuthor Commented:
This works thanks ahammar for the solution
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now