How can I auto-format code which contains no line breaks or indentation?

Question

When working with any Excel function that goes more than a few levels deep, it quickly becomes hard to read in the single-line format Excel presents them in: =IF(F3,24*IF(AC3,IF(ROW()=16,V3,MIN(IF(OR(1440*SUMIF(AC3:AC$16,TRUE,U3:U$16)>=1440*V3,NOT(AC3)),U3,IF(1440*SUMIF(AC3:AC$16,TRUE,Z3:Z$16)>=1440*V3,Z3,AA3)),V3)),U3),"")

So what I'll often do is, I'll pop it into Sublime and indent it like code, making it immediately easier to analyze:

=IF(
    F3,
    24*IF(
        AC3,
        IF(
            ROW()=16,
            V3,
            MIN(
                IF(
                    OR(
                        1440*SUMIF(AC3:AC$16,TRUE,U3:U$16)>=1440*V3,
                        NOT(AC3)
                    ),
                    U3,
                    IF(
                        1440*SUMIF(AC3:AC$16,TRUE,Z3:Z$16)>=1440*V3,
                        Z3,
                        AA3
                    )
                ),
                V3
            )
        ),
        U3
    ),
    ""
)

I've done this enough times that it would save me time to automate it. I started trying to write a regex to do it, something along the lines of:

find ((?: {4})*)(.*?)\b(IF|AND|OR|MIN|MAX)\( replace \1\2\3(\n \1

But I couldn't get it to work because regex struggled with the recursive indentation. I found a Sublime package that can bundle a series of separate regexes into a single operation, which might work but I'm not sure how I'd approach it.

How can I auto-indent my code like this? Are there any possible alternate approaches I may not know about?

score 1 · Accepted Answer · answered Feb 06 '21 at 10:56

1

Regex is something that remain same all over programming languages. So you can simply use above regex in your preferred lang.

<?php

$data = '
=IF(F3,24*IF(AC3,IF(ROW()=16,V3,MIN(IF(OR(1440*SUMIF(AC3:AC$16,TRUE,U3:U$16)>=1440*V3,NOT(AC3)),U3,IF(1440*SUMIF(AC3:AC$16,TRUE,Z3:Z$16)>=1440*V3,Z3,AA3)),V3)),U3),"")
';

$pattern = "/IF\((.*)\)/i"; //find string starting with "IF(" and ends with ")"
$ident = 4;

while(preg_match($pattern, $data,$match) == 1) //If matches 
{
   preg_match($pattern, $data, $match);
   $spaces = str_repeat(' ', $ident); //Generate space with length of ident
   $data =  str_replace($match[1],"\n".$spaces.$match[1]."\n".$spaces,$data); 
   $ident += 4; //Increase ident by 4
}
echo $data; //Now output data
?>

This would output:

=IF(
    F3,24*IF(
        AC3,IF(
            ROW()=16,V3,MIN(IF(
                OR(1440*SUMIF(
                    AC3:AC$16,TRUE,U3:U$16)>=1440*V3,NOT(AC3)),U3,IF(
                        1440*SUMIF(
                            AC3:AC$16,TRUE,Z3:Z$16
                            )>=1440*V3,Z3,AA3
                        )
                    ),V3
                )
            ),U3
        ),""
    )

http://sandbox.onlinephpfunctions.com/code/b97c528312c03ef556efe3e637e200b73da312e0

answered Feb 06 '21 at 10:56

Gray Programmerz

479
1
5
22

I see, to make things easier you're using regex from within another language, which adds the ability to use helpful constructs like variables. Part of my difficulty was because I was using the Sublime editor's "find and replace" style regex, as opposed to a language where I could just include an iteration variable like you did. Thank you for the help! – David S. Feb 09 '21 at 22:03
@DavidS. its regex. Means it can be used in sublime text as well. – Gray Programmerz Feb 10 '21 at 08:21
Right, the problem I ran into was, as Sublime is just a text editor, I can only use the regex itself, not any of the helper code you used with it, such as the str_repeat and the $ident variable. – David S. Feb 10 '21 at 22:12
1

@DavidS. Does above link helps ? https://stackoverflow.com/a/10888837/14919621 – Gray Programmerz Feb 11 '21 at 08:23
Yeah, I tried it out and it's not too bad. Not quite perfect either, but if I just regex line breaks next to every parenthesis `(?<=$)|(?=$)` then use reindent then I get something mostly okay. – David S. Feb 15 '21 at 03:30
1

Exactly, you can achieve it in several ways. Your method is more simpler and user friendly. Whereas, my method is programmer-only thing. Have good day ! – Gray Programmerz Feb 15 '21 at 07:44
You can also post your own answer and mark as tick. – Gray Programmerz Feb 15 '21 at 07:46

How can I auto-format code which contains no line breaks or indentation?

1 Answers1