Formula to count characters in a formula?

[CasaDeCastro]

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?

[mbarron]

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.

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

[JohnBF]

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.

[CasaDeCastro]

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