CODEWITHSUNDEEP
google-sheets
Will Weld
Will Weld

Reputation: 328

Google Sheets - Concatenate 2 rows across columns into 1 cell

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

Upvotes: 0

Views: 1626

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

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

Upvotes: 5

Related Questions