This time simple to follow how-to article on how to get (combine) multiple column values into a single cell in Google Sheets.

What more! Such multiple columns into single-cell values must be inserted as newlines within a cell. My formula will adhere to this.

Actually, the topic revolves around joining texts in columns in a row (or multiple rows sometimes) to a single cell in a particular format.

For this, there is a good number of Text functions available in Google Sheets. We can use the more flexible TEXTJOIN function here for the said purpose.

Must Check: Google Sheets Function Guide.

Can you tell me where this kind of combining of multiple column values into a single cell will be useful?

Suppose you have a spreadsheet where the address of employees entered in multiple columns as below.

Table # 1:

You may want to combine these values in (above) columns into a single cell but as multiple inline rows for printing as labels or for some other purpose.

Table # 2:

In such cases, instead of copy and paste, which will be a time taking process, you can use my formula.

Multiple Columns into Multi-Line Single Cell in Google Sheets

A multi-line single cell may be a new term for you. I simply meant to say a single cell with multiple lines (can say ‘rows’) within.

Columns into Multi-Line Single Cell – Non-Array Formula

Using the below formula in cell E1 you can get multiple column values into a single cell value that also properly aligned.

=textjoin(char(10),true,A1:D1)

This formula combines the values in multiple cells (columns) into a single cell.

I can very easily make you understand how this formula combines values and insert new rows (multi-lines) within a cell.

Syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2, …])

I have used the CHAR formula char(10) as the delimiter (the separator between the joined values in the cells A1:D1.

It inserts an invisible newline character which forces the combined texts to move to a new line.

If you have multiple records to merge into single-cell as above, simply drag the formula down. The following formula is in F1 which then copy-pasted to F2.

=textjoin(char(10),true,A1:D1)

Columns into Multi-Line Single Cell – Array Formula

If you like Array Formula, you may most probably have tried the below formula in cell F1.

=textjoin(char(10),true,A1:D)

Then this;

=ArrayFormula(textjoin(char(10),true,A1:D))

Unfortunately, both the formulas will return the same result and may not satisfy your requirements.

When multiple column values into single-cell involve multiple records (rows), instead of a copy-paste formula we can use an array formula as below in cell F1.

=transpose(split(textjoin(char(10),true,{ArrayFormula("~"&A1:A),B1:D}),"~"))

The logic is simple. Before joining the texts, added the ~ sign at the beginning of the values in the first column (here names) in each row.

After joining the texts, using SPLIT, split the joined columns into its own rows.

Try the above formula without the TRANSPOSE function. Then you will understand the relevance of it in the formula.

Resources – New Lines, Text Join and Split

1. How to Split Text to Columns or Rows in Google Sheets by Delimiter.
2. Split to Column and Categorize – Google Sheets Formula.
3. Split a Column into Multiple N Columns in Google Sheets.
4. How to Move New Lines in a Cell to Columns in Google Sheets.
5. Start New Lines Within a Cell in Google Sheets – Desktop and Mobile.
6. Combine Text and Date in Google Doc Spreadsheet Using Formula.
7. The Flexible Array Formula to Join Columns in Google Sheets.
8. How to Remove Extra Delimiter in Google Sheets – Join Columns.