Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Replace: Formula is Too Long

  1. #1
    Join Date
    30th June 2006
    Posts
    146

    Replace: Formula is Too Long

    Hello,

    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?


    Thanks

    Jeff

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    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.
    MS MVP - Excel

  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    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. #4
    Join Date
    30th June 2006
    Posts
    146

    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.

    http://www.ozgrid.com/forum/showthread.php?t=29700

    VB:
    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 ?


    Thanks

    Jeff

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    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. #6
    Join Date
    30th June 2006
    Posts
    146

    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.


    ~Jeff

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Replace: Formula is Too Long

    You would nest them like;

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

  8. #8
    Join Date
    30th June 2006
    Posts
    146

    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

    Jeff

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replace Method Error When Replacement Too Long
    By farful in forum EXCEL HELP
    Replies: 2
    Last Post: October 4th, 2007, 03:10
  2. Search & Replace Error: Formula Is Too Long
    By jgmhp2 in forum EXCEL HELP
    Replies: 5
    Last Post: May 15th, 2007, 21:07
  3. formula is too long
    By lindali in forum EXCEL HELP
    Replies: 3
    Last Post: September 10th, 2005, 12:08
  4. Formula is too long???
    By cycomcorp in forum EXCEL HELP
    Replies: 7
    Last Post: August 5th, 2005, 07:30
  5. Formula too long
    By AlexDe in forum EXCEL HELP
    Replies: 8
    Last Post: October 8th, 2004, 04:36

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno