Forum Discussion

leolapa's avatar
leolapa
Brass Contributor
Feb 03, 2023
Solved

Join names (TEXTJOIN?) with one-cell 2D spilled formula

Below is a snippet of a list of names that follows some pre-specified criteria:     How I got there doesn't really matter, but the fact that I was able to get to this point via a dynamic for...
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Feb 03, 2023

    leolapa 

    =BYCOL(L57:R58,LAMBDA(col,TEXTJOIN("&",,col)))

    You can try this formula.

     

mtarler's avatar
mtarler
Silver Contributor
Feb 03, 2023

=INDEX($L$57#,1,) & IF(LEN(INDEX($L$57#,2,)), " & " & INDEX($L$57#,2,), "")
or
=TAKE($L$57#,1) & IF(LEN(TAKE($L$57#,-1)), " & " & TAKE($L$57#,-1), "")

  • leolapa's avatar
    leolapa
    Brass Contributor
    Feb 06, 2023
    mtarler they're both pretty good solutions albeit only covering as far as 2 rows by X columns ranges.
    I suppose a recursive LAMBDA approach on top of the INDEX option would address the issue of covering any length of rows, but OliverScheurich's BYCOL solution above does take care of that with a much shorter formula.
    Thanks a lot!

Resources