Pretty Print Excel Formulas?

Question

Does anyone know of a (free) tool to pretty print Excel formulas? A Google search didn't turn anything up.

I've got a few worksheets of semi-complex formulas to slog through, so this would make my life a bit easier.

I'm just looking to turn something like this

AC6+AD6+(IF(H6="Yes",1,IF(J6="Yes",1,0)))+IF(X6="Yes",1,0)

into something more readable without manually doing it in Vim or the like. Excel does do color-matching on the parentheses, but it's still all mashed together on one line.

Answer 1

Here's a commercial solution that may work for you:

http://www.uts.us.com/ItemDetails.asp?ItemID=1100-40-0000-00

A trial version is apparently available.

Answer 2

This VBA code will win no awards, but it's O.K. for quickly looking at typical formulas. It just does what you'd do with parens or separators manually. Stick it in a code module and call it from the VBA immediate window command line.

Public Function ppf(formulaStr As String) As String

Dim tabs(0 To 99) As Long

Dim tabNum As Long
tabNum = 1

Dim tabOffset As Long

Dim i As Long
Dim c As String
For i = 1 To Len(formulaStr)
    c = Mid$(formulaStr, i, 1)

    If InStr("({", c) > 0 Then
        ppf = ppf & c

        tabNum = tabNum + 1
        tabs(tabNum) = tabs(tabNum - 1) + tabOffset + 1
        tabOffset = 0

        ppf = ppf & vbCrLf & Space(tabs(tabNum))
    ElseIf InStr(")}", c) > 0 Then
        tabNum = tabNum - 1
        tabOffset = 0

        ppf = ppf & c & vbCrLf & Space(tabs(tabNum))
    ElseIf InStr("+-*/^,;", c) > 0 Then
        tabOffset = 0

        ppf = ppf & c & vbCrLf & Space(tabs(tabNum))
    Else
        ppf = ppf & c

        tabOffset = tabOffset + 1
    End If
Next i

End Function

If you call it like so:

?ppf([q42].formula)

you don't have to worry about escaping your double quotes and so on. Unfortunately stackoverflow eats all my whitespace when I try to paste in the results of calling it...