I can suggest you different functions to concatenate columns in Google Sheets. It include CONCAT, QUERY and the Ampersand Sign. Of course, the CONCATENATE function is not on the list as it’s not for concatenating columns.
Read: Google Sheets Functions Guide.
Let’s start with concatenate two columns first. But before proceeding further lean how to use the CONCAT function in Google Sheets.
How to Use the CONCAT Function in Google Sheets
Syntax:
CONCAT(value1, value2)Concat Formula Example:
=concat("info","inspired")The above CONCAT formula will return the string “infoinspired”. Type “info” in cell A1 and “inspired” in cell B1. Then the formula will be as follows.
=CONCAT(A1,B1)How to Concatenate Two Columns in Google Sheets
When you want to concatenate two columns use the ArrayFormula with Concat as below.
Actually, the function CONCAT in Google Sheets is ‘equal’ to the Ampersand sign usage. Then how to concatenate two columns in Google Sheets Using the Ampersand?
Here I am replacing the just above Concat formula with the Ampersand.
=ArrayFormula(A2:A9&B2:B9)How to Concatenate Morethan Two Columns in Google Sheets
To concatenate more than two columns, you can use the Ampersand or Query. The Concat function won’t support concatenating of more than two columns.
The Ampersand is easy to use to concatenate more than two columns but there is a draw-back. First, see how to concatenate three + columns using the Ampersand symbol.
Most of the cases, you can use the ampersand to concatenate more than two columns in Google Sheets. There is two drawbacks of this formula.
- If the number of columns is large, as with the same case of many other formulas, you may find it tough to code the formula without any typo.
- Insert one column between the columns already concatenated. To include that column in the formula, you must edit the formula. For example, in the above sample data, insert a new column between B and C. Enter values in that column. Now you must edit the formula to include that column.
Query helps us to overcome this limitation.
Concatenate a Large Number of Columns Using Query in Google Sheets
Formula:
You can use this Query to concatenate a large number of columns in Google Docs Sheets. You only need to change the range in the above formula on your Sheets.
=transpose(Query(transpose(A2:D9),,9^9))The above formula concatenate four adjoining columns, i.e, column A, B, C, and D.
What about non-adjacent columns?
In such cases, I recommend using the ampersand. But if you are so much particular, you can use Query too.
I mean if first few columns are adjoining and there are some other non-adjacent columns, you can consider Query.
How you could learn how to Concatenate Columns in Google Sheets. Enjoy!
So I’m not trying to extract the string from another line in the same cell I am trying to make a list separated by a line break from strings in separate cells
Right now I am doing the following:
=Sheet 1!E:E&" "&Sheet 2!E:E&" "&Sheet 3!E:EWhich will give me whatever is entered into the E column of that cell in each sheet into a single cell separated by spaces. Eg: if I enter A in Sheet 1 B in Sheet 2 and C in Sheet 3 it will output A B C in the target cell.
Is there a way to insert a linebreak where I currently have my spaces so that the list would read
A
B
C
Within the same cell
As opposed to
A B C ?
Thanks!
Hi, Adam,
Thanks for the clarification.
The character that represents the new line can be generated using the CHAR function as below.
=CHAR(10)You can use this formula instead of ” ” in your formula as below.
="Info"&char(10)&"inspired"So your formula will be like;
=ArrayFormula('Sheet 1'!E:E&char(10)&'Sheet 2'!E:E&char(10)&'Sheet 3'!E:E)Hope this helps.
I’m trying to find a way to concatenate a string from a cell with a string from another cell that starts on the next line within the same cell. Is there a way to move to the next line within a formula?
Hi, Adam,
To extract the string after the first line in the same cell, you can use the below formula.
=mid(D4,find(char(10),D4)+1,len(D4))Please replace D4 with the cell that contains your string.