Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2010
    Location
    Redwood City, CA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there a way to count the number of characters in an Excel formula? I tried using the LEN() function, but that only returns info on the results of the formula, not the formula itself, even if I have the formulas displayed.

    I have an Excel 2007 spreadsheet that I have to save in a binary format because I am getting an error that I have a formula that has more than 8192 characters, but I can't figure out which formula it is to see if I can shorten it at all. I would prefer to save it as an regular Excel file, but I need to find this wonky formula first.

    Any ideas how I can find the formula? I am guessing that the formula is so long because it links to a number of cells in other files on our network, and the entire network address got listed in the formula for each cell referenced. But this financial spreadsheet has 36,000 populated cells, a large number of them being external references, so I haven't been able to find the bad formula using old fashioned methods (i.e., actually looking for it).

    Any ideas?

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You could run something like the following. It will change the background color of any cell containing a formula with a length greater than 8000 character red. If conditional formats are in place for a particular cell, the color change will not occur.

    Code:
    Sub findLong()
    Dim cCell As Range
    For Each cCell In Range("a1").CurrentRegion
    	If Len(cCell.Formula) > 8000 And cCell.HasFormula Then
     	With cCell
     	.Interior.ColorIndex = 3
     	End With
    	End If
    Next
    End Sub

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    Quote Originally Posted by Gary Castro View Post
    Is there a way to count the number of characters in an Excel formula?
    For a cool named formula solution see Post 814013 by Rory, but substitute this simpler formula in the Refers To box:

    =LEN(GET.CELL(6,INDIRECT("RC[-1]",FALSE)))

    Note: as explained by Slinky later in the thread, you create this as a range name, such as "FUNCTIONLENGTH", not as a formula in a cell. Then enter the range name =FUNCTIONLENGTH with no arguments in the cell to the immediate right of the formula whose length you want to derive. The "=" sign is counted as a character.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. The Following User Says Thank You to JohnBF For This Useful Post:

    JohnS0603 (2015-08-15)

  5. #4
    New Lounger
    Join Date
    Jan 2010
    Location
    Redwood City, CA
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That formula worked great but I never did solve Excel's problem. My longest formula was 113 characters. I still kept getting an error message when trying to save as an xlsx file instead of an xlsb file, saying that one or more of my formulas was over 8192 characters long.

    I deleted all worksheets from the spreadsheet (including hidden worksheets) except the one causing this problem. I deleted all data from the remaining worksheet, such that when I hit ctrl-end it went to cell A1 (which was empty). I deleted all defined names and there were no longer any links to other files. I still got the error.

    So I tried just cutting and pasting the cells from the original worksheet into a new worksheet in the same file, and I stopped getting the error. So my usability issue is solved but I wonder what was going on with Excel. It was a very Quicken-like problem, but not something you usually see in a high quality program! ;-)

    Thanks for the help

    -Gary

Posting Permissions

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