Search and replace (formula is too long)

[smaon]

Version: Excel 2002

I have a very large spreadsheet with most cells containing very long paragraphs (nature of the legal field). I am having to search and replace a particular word/name (e.g., search Kim and replace it with Sammi), but I receive a "Formula is too long" message.

I have tried the following macro, however, it appears to stop searching and replacing at some point. That is, there were several Kim names that were not replaced with Sammi. Any ideas?

Sub TextSearch()

Dim i As Integer

For i = 1 To 256  'column i = 256
    ActiveSheet.Columns(i).Replace "Kim", "Sammi"
Next

End Sub

 

[Texas Longhorn]

Not sure why Find and Replace won't work...is the text that includes "Kim" entered as a formula in the sheet (e.g. =SUM(A1:A5)&"Kim")? If so, and the formula is long, changing from Kim to Sammie might make the formula too long.

 

[KleptoOne]

Remeber Excel will only accept a formula with a maximum of 1024 characters, so if by replaceing the text, you are going over that 1024 char limit, excel will not accept that.

 

[smaon]

Hmmm, don't know why it's not working.

I just created long text in A1 (867 characters) with nothing else in the worksheet, and I still get formula is too long message. When I cut the long text to about 598 characters, the search and replace works.

I'm very perplexed. If it's working for ya'll, I'm guessing it's a setting here in our office.

 

[Texas Longhorn]

I just tried it, and I'm getting the same thing. I'm not sure where the exact limitation is, or if there is any way around it, but I figured you might feel better knowing that it's not just you!!

 

[erik.van.geit]

Could you use a "conditional-replace"
something like this

Sub replace_if_formula_to_long()
Dim word1 As String, word2 As String
Dim cell As Range
word1 = "Kim"
word2 = "Sammi"
For Each cell In Cells.SpecialCells(xlCellTypeFormulas)
If cell Like "*" & word1 & "*" Then
  If Len(cell) < 555 Then
  cell.Replace what:=word1, Replacement:=word2
  Else
  cell.Interior.ColorIndex = 6
  'or put it in a list
  End If
End If
Next cell
End Sub

code with error handling would be even better: so you won't have to set a maximum number of characters

try this

Sub test_max_formula_length()
Dim aa As String
On Error GoTo finale
aa = ""
Do
aa = aa & "+1"  'or "+11" or 1111 ...
Range("A1").Formula = "=" & aa
Loop
finale:
MsgBox Len(aa)
End Sub

kind regards,
Erik

 

[KenWright]

Try a test replacing Kim with Bob, then with Bobb, then with Bobby etc and see where it bugs out. There is a limit to formula length, and you may well be at the limit in sone or more of your formulas, and trying to replace part of it with a longer piece of text is pushing it over the edge.

You might then want to run a piece of code through each sheet checking the max formula length on each sheet.

 

[erik.van.geit]

This is working for me: no maximum_indication needed anymore.

kind regards,
Erik

Sub replace_and_mark_formula_errors()
Dim word1 As String, word2 As String
Dim cell As Range
word1 = "Kim"
word2 = "Sammi"
For Each cell In Cells.SpecialCells(xlCellTypeFormulas)
  If cell Like "*" & word1 & "*" Then
  On Error Resume Next
  cell = Right(cell.Formula, Len(cell.Formula) - 1)
  cell.Replace what:=word1, Replacement:=word2
  cell = "=" & cell
  If Left(cell.Formula, 1) <> "=" Then cell.Interior.ColorIndex = 6
  End If
Next cell
End Sub

 

[smaon]

I'm not much of a VBA writer, I know just enough to get into trouble. However, with the code above, I get an error on:

For Each cell In Cells.SpecialCells(xlCellTypeFormulas)

Your assistance is much appreciated.

 

[erik.van.geit]

you didn't specifie the error
was it "no cells found"?
then you've got no cells with formulas ?

what's the matter here?
or did you just try out on a sheet without formulas

kind regards,
Erik

 

[smaon]

Even though the error messag indicates the "formula is too long" there are no formulas in the spreadsheet at all -- just very long text. To replicate the issue at question, type "The quick brown fox" phrase, add "Kim" somewere in that phrase, then copy that the phrase and Kim's name 20 times in that same cell so that that cell contains very long text.

In reality, the worksheet I'm working with is very long with lots of different text/description in each cell. But just try that very long phrase (no formula) in one cell and run a search for "Kim" and replace with your name. You should get a "formula is too long" error message.

I have had users on Excel 2000, 2002 and 2003 test this for me, and they are all getting the same error message. As such, I believe a macro must be created to fix this issue. [I'm not sure how those who previously responded got the find/replace to work. I can only guess that the text was probably not long enough.]

Thanks!