8

Based on most popular answer:

Tl;dr: to use the Swift language rules, add <!-- language-all: lang-swift --> to the top of your post, or use ```lang-swift at the beginning of each code block where you desire highlighting.


Highlight.js doesn't support the Excel formula language and I doubt SE would add it to the list of styles to support on the network. This has been asked for at Meta Stack Overflow too.

What's a good alternative?

For example this code:

=LAMBDA
(
  vArg,
  LET
  (
    v,    INT(ABS(vArg)),
    ev,   ISEVEN(v),
    half, 1/2,
    high, INT(SQRT(v)+half),
    seqLimit, 2^20,
    IF
    (
      AND(ev, v<>2),
      0, 
      IF
      (
        high>seqLimit,
        -1, 
        LET
        (
          high,         INT(SQRT(v)+1/2),
          seq,          v/SEQUENCE(1,high,1,2),
          iSeq,         INT(seq),
          factors,      seq*(seq=iSeq),
          rFactors,     FILTER(factors,(factors>1)*(factors<v),0),
          sumFactors,   SUM(rFactors)
          --(sumFactors=0) 
        )
      ) 
    )
  )
)

is rendered using the tag based ( or ) or auto recognition syntax highlighting like this:

Rendered code which is not perfect as the --(sumFactors=0) line is rendered like a comment to me which is wrong, and formula names ISEVEN, FILTER etc. are highlighted inconsistently.

Any suggestions of similar supported languages that may look good?

5
  • 1
    This doesn't look like anything Inwould put in an excel cell.
    – Luuklag
    Mar 20, 2021 at 21:10
  • 2
    @Luuklag I just found thisLAMBDA function. It suggest to add the formula in a cell to test it before add it to the name manager.
    – Rubén
    Mar 20, 2021 at 22:12
  • @Luuklag, it's a new (beta channel) function so you may not have seen it before but it's nothing special, identical syntax to the more vanilla functions. Just been through a formula prettifier, but whitespace doesn't matter to Excel
    – Greedo
    Mar 20, 2021 at 23:26
  • @Greedo What tool do you use to format your formulas? Jun 14, 2021 at 18:32
  • 1
    @JimGrisham excelformulabeautifier.com
    – Greedo
    Jun 15, 2021 at 9:25

3 Answers 3

5

Try swift

=IF($A1>6,"Hello","Goodbye")

=LAMBDA
(
  vArg,
  LET
  (
    v,    INT(ABS(vArg)),
    ev,   ISEVEN(v),
    half, 1/2,
    high, INT(SQRT(v)+half),
    seqLimit, 2^20,
    IF
    (
      AND(ev, v<>2),
      0, 
      IF
      (
        high>seqLimit,
        -1, 
        LET
        (
          high,         INT(SQRT(v)+1/2),
          seq,          v/SEQUENCE(1,high,1,2),
          iSeq,         INT(seq),
          factors,      seq*(seq=iSeq),
          rFactors,     FILTER(factors,(factors>1)*(factors<v),0),
          sumFactors,   SUM(rFactors),
          --(sumFactors=0) 
        )
      ) 
    )
  )
)

highlight.js actually supports Excel (ref: Supported Languages) but it's not supported by the highlighter version of SE.

I'm not familiar with Excel Lambda functions but it looks that the problem is the formula, not the highlighting. I.E, the following formula taken from another post from Code Review, the highlight is applied correctly there

=IF(OR(ISBLANK(X2),ISBLANK(Y2)),0,X2/VLOOKUP(YEAR(Y2),$EU$3:$FH$57,14,FALSE))*$FH$57

Rendered version of the above formula taken from the source

NOTE: Using the inspect tool, the auto-detect function assigned swift to the above formula and sql to the formula in the question.

I just quickly scanned LAMBDA function. As far I can tell, it looks that the Lambda function in the question is not using the regular spreadsheet formula syntax as there is no separator before --(sumFactors=0). But that is not the unique problem b/c after adding the comma, Excel still shows:

P.S. looking at the source of the formula the OP mentioned that the formula doesn't work.

Related

6
  • Oh yeah, there should be a comma, it's not my code - I got it from here codereview.stackexchange.com/q/257331/146810. But I tried editing the question to add the comma and it still renderers wrong. Besides the inconsistent formula name highlighting is a bigger problem for readability.
    – Greedo
    Mar 20, 2021 at 23:20
  • 1
    @Greedo Please consider to add a better formula example
    – Rubén
    Mar 20, 2021 at 23:53
  • Unfortunately not imported by SO: dev.sstatic.net/js/highlightjs-loader.en.js does not reference any of the tags for excel/xls/xlsx (point 3 here). As for the formula, it's very new beta channel so may not be available to you, and requires an additional set of parens =LAMBDA(...)(...) to invoke it. But my question with correct syntax is highlighted wrong: [ 1 ], [ 2 ]. I like the example you give, different language tag maybe?
    – Greedo
    Mar 21, 2021 at 0:37
  • 1
    Thanks, swift looks good, even though its syntax looks totally different to Excel formulae, strange! I'll leave this unaccepted in case someone finds an even nicer looking highlighting tag
    – Greedo
    Mar 23, 2021 at 9:53
  • Thanks -- setting the language as Swift works great in both VSCode and Notepad++ (and likely other editors with Swift support)! Jun 14, 2021 at 18:21
  • 2
    Tl;dr: to use the Swift language rules on StackExchange, add "<!-- language-all: lang-swift -->" to the top of your post, or use "``` lang-swift" at the beginning of each code block where you desire highlighting. Jun 14, 2021 at 18:29
4

Try using Scala (lang-scala)

Example:

=LAMBDA
(
  vArg,
  LET
  (
    v,    INT(ABS(vArg)),
    ev,   ISEVEN(v),
    half, 1/2,
    high, INT(SQRT(v)+half),
    seqLimit, 2^20,
    IF
    (
      AND(ev, v<>2),
      0, 
      IF
      (
        high>seqLimit,
        -1, 
        LET
        (
          high,         INT(SQRT(v)+1/2),
          seq,          v/SEQUENCE(1,high,1,2),
          iSeq,         INT(seq),
          factors,      seq*(seq=iSeq),
          rFactors,     FILTER(factors,(factors>1)*(factors<v),0),
          sumFactors,   SUM(rFactors)
          --(sumFactors=0) 
        )
      ) 
    )
  )
)

As far as I can tell, this works well with minified formula.

Example for minified formula:

=LET(hello,"Hello World, ",hello & Sheet1!$A2 & "!")
2
3

Adding syntax highlighting for Excel Formula would be a good thing. Ideally it would be specific to Excel formula (ie as supported by highlight.js), however if that's unlikely to be supported on SO/SE then utilising another language would be acceptable. The question is: which one.

Whichever is chosen, it should

  • give useful highlights to the various components of a formula

  • give reasonable results on most (if not all) common use cases

  • give unhelpful results on as few as possible edge cases

The two suggested languages are compared below

Swift

=A1+A2
=$A$1+A2
=A$1+A4
=$A1+A5
=$A$1:A10+B10:B100
=SUM(A1:A10)
=SUM(R1C1:R[9]C1+RC[-3]:RC[-3])
=MyRange
=MyUDF(A1:A10,B1,"Hi",-C1+D1,MyRange)
=D1#
=@D1
=@D1:D100
=XLOOKUP(J1,H1:H10,D1:D10,"A1",0,1)
=--(A1:A10=0)
=IF($A1>6,"Hello","Goodbye")
=LAMBDA(base,width,paddingStr, IF(LEN(base)<=width, base, LET(LHS, LEFT(base, width), RHS, RIGHT(base, LEN(base) - width), LHS & paddingStr & PadInternal(RHS,width,paddingStr))))

Scala

=A1+A2
=$A$1+A2
=A$1+A4
=$A1+A5
=$A$1:A10+B10:B100
=SUM(A1:A10)
=SUM(R1C1:R[9]C[-4]+RC[-3]:R[9]C[-3])
=MyRange
=MyUDF(A1:A10,B1,"Hi",-C1+D1,MyRange)
=D1#
=@D1
=@D1:D100
=XLOOKUP(J1,H1:H10,D1:D10,"A1",0,1)
=--(A1:A10=0)
=IF($A1>6,"Hello","Goodbye")
=LAMBDA(base,width,paddingStr, IF(LEN(base)<=width, base, LET(LHS, LEFT(base, width), RHS, RIGHT(base, LEN(base) - width), LHS & paddingStr & PadInternal(RHS,width,paddingStr))))

Based on this, I'd suggest Swift be ruled out, as it gives inconsistent results for range references, and mangles some ranges.

Scala is better, but doesn't handle Implicit Intersection ranges well

2
  • This is Community Wiki so anyone can add more example formula and/or languages. Jun 29 at 0:12
  • Some more edge cases; linebreaks, various operators like / which might trip it up, table references
    – Greedo
    Jun 29 at 13:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .