PRETTYPRINT

[tboulden]

PRETTYPRINT inserts new lines and indents based on depth with respect to opening and closing bracket characters (parentheses () and curly braces {}) and commas.

PrettyPrint's output displays best with a monospace font like Lucida Console in Excel.

Note that this is a naive implementation as I'm not trying to account for whether opening and closing brackets are inside of double-quotes (as happens in the definition of the LAMBDA and can be seen in the XL2BB snippet below). Also note this could all be done in a LET statement; the cumTot LAMBDA is just to save space here. The XL2BB snippet is the output of this code on its own definition.

I have an improvement basically ready to go, however my helper LAMBDA is misbehaving in a yet unaccounted for manner; will include the improvement if I figure out the issue.

=LAMBDA(
    cellRefOrTxt,
    indent,
    LET(
        formTxt,
        IF(
            ISTEXT(cellRefOrTxt),
            cellRefOrTxt,
            FORMULATEXT(cellRefOrTxt)
        ),
        strLen,LEN(formTxt),
        seq,SEQUENCE(strLen),
        cumTot,
        LAMBDA(
            list,
            MMULT(
                TRANSPOSE(
                    (seq<=TRANSPOSE(seq))*list
                ),
                SIGN(list)
            )
        ),
        strToArr,MID(formTxt,seq,1),
        isLBr,--((strToArr="("+(strToArr="{")),
        isRBr,--((strToArr=")")+(strToArr="}")),
        isComma,--(strToArr=","),
        newLine,isLBr+isRBr+isComma,
        cumLBr,cumTot(isLBr),
        cumRBr,-cumTot(isRBr),
        tabCt,cumLBr+cumRBr,
        whiteSpace,
        IF(
            newLine,CHAR(10),
            ""
        )&REPT(" ",indent*newLine*tabCt),
        concArr,
        IF(
            isRBr,whiteSpace,
            ""
        )&strToArr&
        IF(
            isLBr+isComma,whiteSpace,
            ""
        ),
        pretty,TEXTJOIN("",FALSE,concArr),
        pretty
    )
)

LAMBDA_Testing_PrettyPrint.xlsm
	DQ	DR	DS	DT	DU	DV	DW	DX	DY
3	Anonymous LAMBDA:		#CALC!
4
5	=LAMBDA( cellRefOrTxt, indent, LET( formTxt, IF( ISTEXT( cellRefOrTxt ), cellRefOrTxt, FORMULATEXT( cellRefOrTxt ) ), strLen, LEN( formTxt ), seq, SEQUENCE( strLen ), cumTot, LAMBDA( list, MMULT( TRANSPOSE( ( seq<=TRANSPOSE( seq ) )*list ), SIGN( list ) ) ), strToArr, MID( formTxt, seq, 1 ), isLBr, --( ( strToArr="( " )+( strToArr="{ " ) ), isRBr, --( ( strToArr=" )" )+( strToArr=" }" ) ), isComma, --( strToArr=", " ), newLine, isLBr+isRBr+isComma, cumLBr, cumTot( isLBr ), cumRBr, -cumTot( isRBr ), tabCt, cumLBr+cumRBr, whiteSpace, IF( newLine, CHAR( 10 ), "" )&REPT( " ", indent*newLine*tabCt ), concArr, IF( isRBr, whiteSpace, "" )&strToArr&IF( isLBr+isComma, whiteSpace, "" ), pretty, TEXTJOIN( "", FALSE, concArr ), pretty ) )
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
Scratch

Cell Formulas
Range	Formula
DS3	DS3	=LAMBDA(cellRefOrTxt,indent,LET(formTxt,IF(ISTEXT(cellRefOrTxt),cellRefOrTxt,FORMULATEXT(cellRefOrTxt)),strLen,LEN(formTxt),seq,SEQUENCE(strLen),cumTot,LAMBDA(list,MMULT(TRANSPOSE((seq<=TRANSPOSE(seq))*list),SIGN(list))),strToArr,MID(formTxt,seq,1),isLBr,--((strToArr="(")+(strToArr="{")),isRBr,--((strToArr=")")+(strToArr="}")),isComma,--(strToArr=","),newLine,isLBr+isRBr+isComma,cumLBr,cumTot(isLBr),cumRBr,-cumTot(isRBr),tabCt,cumLBr+cumRBr,whiteSpace,IF(newLine,CHAR(10),"")&REPT(" ",indent*newLine*tabCt),concArr,IF(isRBr,whiteSpace,"")&strToArr&IF(isLBr+isComma,whiteSpace,""),pretty,TEXTJOIN("",FALSE,concArr),pretty))
DQ5	DQ5	=PrettyPrint.Naive(DS3,4)

 

[tboulden]

Re-oriented from cell DS3 to cell A1 per @smozgur suggestion in a different thread. Oops!

LAMBDA_Testing_PrettyPrint.xlsm
	A	B	C	D	E	F	G	H	I
1	Anonymous LAMBDA:		#CALC!
2
3	=LAMBDA( cellRefOrTxt, indent, LET( formTxt, IF( ISTEXT( cellRefOrTxt ), cellRefOrTxt, FORMULATEXT( cellRefOrTxt ) ), strLen, LEN( formTxt ), seq, SEQUENCE( strLen ), cumTot, LAMBDA( list, MMULT( TRANSPOSE( ( seq<=TRANSPOSE( seq ) )*list ), SIGN( list ) ) ), strToArr, MID( formTxt, seq, 1 ), isLBr, --( ( strToArr="( " )+( strToArr="{ " ) ), isRBr, --( ( strToArr=" )" )+( strToArr=" }" ) ), isComma, --( strToArr=", " ), newLine, isLBr+isRBr+isComma, cumLBr, cumTot( isLBr ), cumRBr, -cumTot( isRBr ), tabCt, cumLBr+cumRBr, whiteSpace, IF( newLine, CHAR( 10 ), "" )&REPT( " ", indent*newLine*tabCt ), concArr, IF( isRBr, whiteSpace, "" )&strToArr&IF( isLBr+isComma, whiteSpace, "" ), pretty, TEXTJOIN( "", FALSE, concArr ), pretty ) )
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
Sheet1

Cell Formulas
Range	Formula
C1	C1	=LAMBDA(cellRefOrTxt,indent,LET(formTxt,IF(ISTEXT(cellRefOrTxt),cellRefOrTxt,FORMULATEXT(cellRefOrTxt)),strLen,LEN(formTxt),seq,SEQUENCE(strLen),cumTot,LAMBDA(list,MMULT(TRANSPOSE((seq<=TRANSPOSE(seq))*list),SIGN(list))),strToArr,MID(formTxt,seq,1),isLBr,--((strToArr="(")+(strToArr="{")),isRBr,--((strToArr=")")+(strToArr="}")),isComma,--(strToArr=","),newLine,isLBr+isRBr+isComma,cumLBr,cumTot(isLBr),cumRBr,-cumTot(isRBr),tabCt,cumLBr+cumRBr,whiteSpace,IF(newLine,CHAR(10),"")&REPT(" ",indent*newLine*tabCt),concArr,IF(isRBr,whiteSpace,"")&strToArr&IF(isLBr+isComma,whiteSpace,""),pretty,TEXTJOIN("",FALSE,concArr),pretty))
A3	A3	=PrettyPrint.Naive(C1,4)

 

[tboulden]

This is a bit more opaque, although not being recursive, so commented lines below:

=LAMBDA(
    cellRefOrTxt,
    indent,                                        // # of spaces for each indent
    LET(
        formTxt,                                   // gets ONLY text from cellRefOrTxt
        IF(
            ISTEXT(cellRefOrTxt),
            cellRefOrTxt,
            FORMULATEXT(cellRefOrTxt)
        ),
        strLen,LEN(formTxt),
        seq,SEQUENCE(strLen),
        cumTot,                                   // creates a 1-d array with running total
        LAMBDA(                                   \\ of values from top-to-bottom
            list,
            MMULT(
                TRANSPOSE(
                    (seq<=TRANSPOSE(seq))*list
                ),
                SIGN(list)
            )
        ),
        strToArr,MID(formTxt,seq,1),              // create array to perform logical tests
        isLBr,--((strToArr="("+(strToArr="{")),   // check if char is a left bracket
        isRBr,--((strToArr=")")+(strToArr="}")),  // check if char is a right bracket
        isComma,--(strToArr=","),                 // check if char is a comma
        newLine,isLBr+isRBr+isComma,              // new line if either 3; + works like OR here
        cumLBr,cumTot(isLBr),                     // running count of all opening (+1) brackets
        cumRBr,-cumTot(isRBr),                    // running count of all closing (-1) brackets
        tabCt,cumLBr+cumRBr,                      // opening count - closing count = # of tabs
        whiteSpace,
        IF(
            newLine,CHAR(10),                     // insert new line if bracket or comma
            ""
        )&REPT(" ",indent*newLine*tabCt),         // insert required # of spaces base on depth
        concArr,
        IF(
            isRBr,whiteSpace,                     // whitespace goes BEFORE closing bracket
            ""
        )&strToArr&
        IF(
            isLBr+isComma,whiteSpace,             // whitespace goes AFTER opening bracket or comma
            ""
        ),
        pretty,TEXTJOIN("",FALSE,concArr),
        pretty
    )
)