Search and replace (formula is too long)

smaon · Feb 18, 2005

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?

Code:

Sub TextSearch()

Dim i As Integer

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

End Sub

erik.van.geit · Feb 18, 2005

Hi,

WELCOME to the Board !!

There can always be unexpected results when not being careful with this kind of operations.
possible problems:
If there is somewhere a name with "kim" like "skimmer" referring to a range.
affecting formulas: Especially short-length-replacements are dangerous: replace "up" by "down" ==> VLOOKUP VLOOKdown ???

using the full syntax, you can do some excluding
Selection.Replace What:="Kim", Replacement:="Sammi", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

it's also good practice to exclude some cells in advance
activesheet.Usedrange.SpecialCells(xlCellTypeConstants, 2).Replace

does this get you furhter?

kind regards,
Erik

smaon · Feb 22, 2005

Thank you very much for your response.

I agree, one should always be cautious with the search and replace feature, especially whe searching and replacing a string of characters that can be within other words. In my instance, the word that I am trying to search is not really "Kim" and replacing it with "Sammi." I am actually searching for a client name that is very unique.

Unfortunately the VBA code you forwarded did not do the trick. Like my code, it only replaced some of the words but not all.

To replicate the issue, type "The quick brown fox" phrase 20 times in one cell, type a unique name somewhere in that cell, and then copy that cell throughout your spreadsheet until it's about 50 pages long. Then try to search the unique name with another name and you'll get the "formula is too long" message. Anyhow, that is the issue that I'm struggling with.

Your help is much appreciated.

smaon · Feb 22, 2005

Thank you very much for your response.

I agree, one should always be cautious with the search and replace feature, especially when searching and replacing a string of characters that can be within other words. In my instance, the word that I am trying to search is not really "Kim" and replacing it with "Sammi." I am actually searching for a client name that is very unique.

Unfortunately the VBA code you forwarded did not do the trick. Like my code, it only replaced some of the words but not all.

To replicate the issue, type "The quick brown fox" phrase 20 times in one cell, type a unique name somewhere in that cell, and then copy that cell throughout your spreadsheet until it's about 50 pages long. Then try to search the unique name with another name and you'll get the "formula is too long" message. Anyhow, that is the issue that I'm struggling with.

Your help is much appreciated.

smaon · Feb 22, 2005

Thank you very much for your response.

I agree, one should always be cautious with the search and replace feature, especially when searching and replacing a string of characters that can be within other words. In my instance, the word that I am trying to search is not really "Kim" and replacing it with "Sammi." I am actually searching for a client name that is very unique.

Unfortunately the VBA code you forwarded did not do the trick. Like my code, it only replaced some of the words but not all.

To replicate the issue, type "The quick brown fox" phrase 20 times in one cell, type a unique name somewhere in that cell, and then copy that cell throughout your spreadsheet until it's about 50 pages long. Then try to search the unique name with another name and you'll get the "formula is too long" message. Anyhow, that is the issue that I'm struggling with.

Your help is much appreciated.

PA HS Teacher · Feb 22, 2005

If you are replacing Sammy with Kim or vice-versa throughout the entire sheet, why bother with code? Why not use replace from the edit menu.

I entered,
"The quick brown fox jumped over Kim and some other words" twenty times in cell A1.

I then copied it down for 10,000 cells. I used the edit replace feature built into excel and it changed Kim to Sammy as desired.

smaon · Feb 22, 2005

I would love to just use the Search and Replace feature but I'm getting a "formula is too long" message.

What version of Excel are you using? I'm on Excel 2002.

scrupul0us · Feb 22, 2005

You dont need a formula... goto:

EDIT > Search and Replace.. then

Search for: KIM
Replace with: SAMMI

hit "replace all" and your done

smaon · Feb 22, 2005

That is strange. You and I seem to be on the same system (Office XP, Windows XP SP1), but a simple search and replace is not working on mine; I continue to receive a "formula is too long" message.

PA HS Teacher · Feb 22, 2005

I am on XP SP2 Excel 2002, I don't know why it wouldn't work.

Try copying a small number of cells to a new workbook and using edit replace there.

If that works try a larger chunk.

If that works try copying the whole chunk.

Search and replace (formula is too long)

smaon

New Member

erik.van.geit

MrExcel MVP

smaon

New Member

smaon

New Member

smaon

New Member

PA HS Teacher

Well-known Member

smaon

New Member

scrupul0us

Well-known Member

smaon

New Member

PA HS Teacher

Well-known Member

Similar threads

Share this page

Search and replace (formula is too long)

New Member

MrExcel MVP

New Member

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock