Google Sheets Concatenate – How to Concatenate Several Columns into One

concatenategoogle sheets

I want to concatenate/combine columns A, B and C into column D as displayed.

The number of filled entries in A, B and C will vary over time, so absolute cell addresses cannot be used.

What formula could I put into D1 to fill the column as illustrated?

You need to filter down to non-empty cells, and then stack the results together:

={filter(A:A, len(A:A)); filter(B:B, len(B:B)); filter(C:C, len(C:C))}

This is the same as this answer by grayob which is unfortunately buried under outdated answers.

This can be done with an arrayformula as follows:

=arrayformula(importrange("URL", "A1:A3") & " " & importrange("URL", "B1:B3"))

The inner part describes what should be done within one row; the arrayformula then says "do it for every row".

Assuming the data is in column B, the following two formulas do the job. In cell C1:

=filter(B1:B, row(B1:B) < 1 + max(filter(row(B:B), len(B:B))) / 2)

In cell D1:

=filter(B1:B, row(B1:B) >= 1 + max(filter(row(B:B), len(B:B))) / 2)

Explanation:

Find the last row with data by max(filter(row(B:B), len(B:B)))
Divided by 2 and use as a threshold for splitting the column: rows above it go to column C, rows below it go to column D.