Google Sheets - Concatenate 2 rows across columns into 1 cell

0
votes

I'd like to achieve what's in the Desired Output column in the image below. I need to take what's in row 1 (id1, id2, etc.), add ":" to that, then concatenate it with the values under each of the Field columns, add "|" to each ID-Value pair, and get that all together into one cell. I need the formula to also work for empty cells, as the number of fields to concatenate together is dynamic.

So far I've tried a big CONCATENATE formula in one cell, but I can only get it to work for as many non-blank cells as I include in the formula.

Thanks in advance!

enter image description here

1
google-sheets
Can you add an example of what you expect when there are empty cells too? What formula are you currently using? – BruceWayne
Excel? Or Google Sheets? Why use the wrong tags? – tehhowch
This is for Google Sheets, my apologies. I figured that there might be a formula that worked for both but maybe not. – Will Weld
@BruceWayne I'm literally just concatenating and it's not going to work dynamically. For empties, I don't want to include the ":" and "|" characters. FWIW, this is my concatenate: =CONCATENATE($D$1, ":", D3, "|", $E$1, ":", E3, "|", $F$1, ":", F3, "|", $G$1, ":", G3) – Will Weld

1 Answers

3
votes

Use JOIN:

=arrayformula(join("|",filter($B$1:$E$1& ":" & B2:E2,B2:E2<>"")))

enter image description here

Use TEXTJOIN

=TEXTJOIN("|",,IF(B2:E2<>"",$B$1:$E$1 & ":" & B2:E2,""))

This will be an array formula and must be confirmed with ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here