Google-sheets – In Google Sheets: How to merge a range of a row (the header) with the range of each column into a single cell

google sheets

I want to have a cell with the header values, then a separator, then the values of a row, then a different separator.
And I want this to happen for each row while the header values remain the same.

Example:
example

Updated Example:
updated example

The solution I'm looking for should work for 50 columns

Best Answer

Formula

Initial formula

Formula for 50 columns

=ArrayFormula(JOIN(CHAR(10),$A$1:$AX$1&":"&$A2:$AX2))

Better formula

Formula for 50 columns and the required number of rows.

=ARRAY_CONSTRAIN(ARRAYFORMULA(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE($A$1:$AX$1&": "&$A2:$AX&CHAR(10)),,1000000)),"\n$","")),COUNTA($A$2:$A),1)

Explanation

Description of formula parts

Initial formula

  • $A$1:$AX$1&":"&$A2:$AX2 creates a text value of headers and values separated by a colon.
  • CHAR(10) returns a carriage return (new line)
  • JOIN joins the values of array of second argument
  • ArrayFormula makes the formula able to make array operations on scalar functions and operators like &

Use instructions

Add the formula to a free cell on row 2 then fill down.

Notes

Using a cell to display row headers and cell values is fine for tables having few columns and not too lengthy values. On large tables, including those having 50 columns or having cells with lengthy values could lead you face some of the following problems:

  1. 50,000 characters limit
  2. The in-cell scroll bar for cells higher than the screen doesn't work

Also on sheets having lot of formulas and/or rows could make the spreadsheet slow or even non usable due to large recalcultation times

In cases that a formula like the one described above were not good, try Visor de Registros (Record Viewer), a Google Sheets free add-on developed by me. Currently it's unlisted but very soon I will send it to review by Google. It was published on August 14, 2018.

Note: The website is in Spanish as it's the first language of the firsts users that help me to test the add-on. I hope that the Google Sites built-in translate feature were good enough, but the add-on UI is available in English and Spanish.

Better formula

This formula is better because it doesn't require to fill down in order to get the desired result for the all the rows in the data range.

Instructions

Add the formula to a free cell on row 2

NOTE: The formula assumes that A2:A values are text and there aren't blanks on the data range.

Related Q's

Related Google editors help article

Related Topic