Results 1 to 4 of 4

20100105, 14:37 #1
 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?

20100105, 14:51 #2
 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

20100106, 14:54 #3
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
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

The Following User Says Thank You to JohnBF For This Useful Post:
JohnS0603 (20150815)

20100910, 13:15 #4
 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 ctrlend 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 Quickenlike problem, but not something you usually see in a high quality program! ;)
Thanks for the help
Gary