No announcement yet.

Replace: Formula is Too Long

  • Filter
  • Time
  • Show
Clear All
new posts

  • Replace: Formula is Too Long


    I'm receiving this message when I attempt to use the Find, Replace function..

    "Formula is too long"

    I have a column of cells containing text only. ( about 2-3 paragraphs worth) I'm trying to replace a name with another name, which works fine where the cell contains a single or few sentences, but fails to replace when the cell contains too much information.

    Is there a way to allow this?



  • #2
    Re: Find, Replace name in Cell limitations

    I think Replace has a 1024-character limit. You could use the SUBSTITUTE worksheet function, or VBA.
    Entia non sunt multiplicanda sine necessitate.


    • #3
      Re: Find, Replace name in Cell limitations

      Jeff, please search before posting. I have edited your thread title to make it even easier for you.


      • #4
        Re: Replace: Formula is Too Long

        Thanks Shg.

        Your suggestion led me to a terrific code by Will Riley, which finds names in A4:A10, and replaces with names in B4:B10 respectively.

        Option Explicit
        Sub String_Replacer()
            Dim ws As Worksheet, wb As Workbook
            Dim fList As Variant, I As Integer
            Dim rng1 As Range
            Dim cel As Range
            Dim strMyChar As String, strMyReplace As String
            With ActiveWorkbook.Worksheets("sheet1")
                Set rng1 = .[A4:A10]
            End With
             'displays the dialog fo rchoosing files to action
            fList = Application.GetOpenFilename(MultiSelect:=True)
             'check and see if cancel selected, which returns a boolean  variable
            If TypeName(fList) = "Boolean" Then
                 MsgBox "No files selected. Activity halted."
                Exit Sub
            End If
             ' Loops through every file that is selected and open
            For I = 1 To UBound(fList)
                 'open the workbook, but do not update  links
                Set wb = Workbooks.Open(fList(I), False)
                 'loop thru sheets used range
                For Each ws In wb.Worksheets
                     'loop down list of text needing replacing
                    For Each cel In rng1.Cells
                        strMyChar = cel.Value
                        strMyReplace = cel.Offset(0, 1).Value
                         'replace text
                        With ws.UsedRange
                            .Replace What:=strMyChar, Replacement:=strMyReplace, _
                            SearchOrder:=xlByColumns, MatchCase:=True
                        End With
                         'next word/text to relace
                    Next cel
                Next ws
                 'close &  save
                wb.Close True
                 'next workbook to do
            Next I
        End Sub

        unfortunetaly when it finds a cell exceeding the 1024 character limit, it stops.

        Is there any way to modify this ?




        • #5
          Re: Replace: Formula is Too Long

          Jeff, did you search as I suggested? When I do, I see many posts with many different ways. Substitute or Replace Function included.


          • #6
            Re: Replace: Formula is Too Long

            Finally managed to get information on Substitute...

            I this example replacing the word hello with Goodbye

            =SUBSTITUTE(A1, "hello", "Goodbye")

            How can I add additional replacement words?

            ie: if I also wanted to include the words "Up" to be replaced with "Down"

            ...trying several variations with no luck.

            Thanks for any help.



            • #7
              Re: Replace: Formula is Too Long

              You would nest them like;

              =SUBSTITUTE(SUBSTITUTE(A1, "hello", "Goodbye"),"Up","Down")


              • #8
                Re: Replace: Formula is Too Long

                Thanks Dave!

                understanding how nested works.

                =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "hello", "Goodbye"),"Up","Down"),"Black","White")

                I may need to add additional name changes as I go.

                Is there a limitation to the amount?.. I read up to 7 levels of formulas.

                .. just wondering

                thanks for the help



                • #9
                  Re: Replace: Formula is Too Long

                  Yep, seven nest formulae limit pre 2007. However, I have always maintained if you need 7, or more, you are doing something wrong.