Forum Discussion

tonyceccato's avatar
tonyceccato
Icon for Microsoft rankMicrosoft
Jan 23, 2026

Help with a Formula

Below is a formula I created to return specified rows and columns for a sheet named Detail and in the case or column 49 detect if there are comma separated values and explode them into separate colum...
excel
Formulas and Functions
training
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Jan 26, 2026
=LET(
  f, FILTER(Detail!$A:$BZ, 
    (COUNTIF($B$16:$B$25, Detail!$BP:$BP) > 0) * 
    (TRIM(SUBSTITUTE(Detail!$AW:$AW, CHAR(160), "")) <> "")),
  base, CHOOSECOLS(f, 1, 3, 4, 5, 68),
  
  split49, MAP(CHOOSECOLS(f, 49), LAMBDA(x,
    LET(
      cleaned, TRIM(SUBSTITUTE(x, CHAR(160), "")),
      IF(cleaned = "", {""}, TEXTSPLIT(cleaned, ","))
    ))),
  
  split53, MAP(CHOOSECOLS(f, 53), LAMBDA(x,
    LET(
      cleaned, TRIM(SUBSTITUTE(x, CHAR(160), "")),
      IF(cleaned = "", {""}, TEXTSPLIT(cleaned, ","))
    ))),
  
  SORT(UNIQUE(HSTACK(base, split49, split53)), 1, TRUE)
)

My answers are voluntary and without guarantee!

 

Hope this will help you.

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jan 27, 2026

    Hi NikolinoDE​ 

    How did you make it work?

    =MAP( {"a,b,c"; "d"; "e,f"},
  LAMBDA(x,
    LET(
      cleaned, TRIM(SUBSTITUTE(x, CHAR(160), "")),
      IF(cleaned = "", {""}, TEXTSPLIT(cleaned, ","))
    )
  )
)   // #CALC!

     

    • NikolinoDE's avatar
      NikolinoDE
      Platinum Contributor
      Jan 27, 2026

      Great question — and sharp catch on the #CALC! 😊

       

      Your test fails because MAP can't return arrays of different widths (3 cols, then 1 col, then 2 cols) — Excel needs a rectangular result. My formula works only if every cell in columns 49 & 53 splits into the same number of items (e.g., all split to 2 values → 2 columns). If splits vary, it also breaks with #CALC!.

       

      =MAP({"a,b,c";"d";"e,f"}, LAMBDA(x, TEXTSPLIT(x,",")))

      Tries to return {a,b,c} (3 cols), then {d} (1 col), then {e,f} (2 cols) → non-rectangular → #CALC!.

       

      My formula works...sometimes

      CHOOSECOLS(f,49) feeds MAP a single column. If every cell splits to the same number of pieces (e.g., always 2 values), Excel builds a clean 2-column spill → HSTACK accepts it.

      The MAP + TEXTSPLIT combo creates temporary arrays inside the formula that #CALC! on their own, but they work perfectly when fed into HSTACK() and UNIQUE().

       

      You cracked the code by spotting the #CALC!—it's exactly why we need HSTACK as the next step. The individual pieces look broken, but together they create the final structure.

      Well spotted, and great debugging!👍

       

      Thanks for sharing it with us🙂.

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Jan 27, 2026

        Hey NikolinoDE​ 

        My question was a bit provocative 😉 This won't work in any case due to nested arrays:

        =MAP( {"1,2,3";"10,20,30"},
  LAMBDA(x,
    LET(
      cleaned, TRIM(SUBSTITUTE(x, CHAR(160), "")),
      IF(cleaned = "", {""}, TEXTSPLIT(cleaned, ","))
    )
  )
)     // #CALC! (Nested arrays)
        =MAP( {"1,2,3";"10,20,30"},
  LAMBDA(x, TEXTSPLIT(x, ",") )
)     // #CALC! (Nested arrays)

        Classic option:

        =LET(
  array,    {"1,,2,3,,4";"10,20,,30"},

  delim,          CHAR(44),
  StackSplitted,  LAMBDA(initial,array,
                    VSTACK( initial, IF( LEN(array), TEXTSPLIT(array, delim,, TRUE ), "" ) )
                  ),
  IFNA( DROP( REDUCE("", array, StackSplitted ), 1 ), "" )
)

         

Resources