 Show
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?
pnuts 17k5 gold badges50 silver badges102 bronze badges asked Mar 6, 2016 at 16:30
2 You need to filter down to non-empty cells, and then stack the results together: This is the same as this answer by grayob which is unfortunately buried under outdated answers. 1 A shorter version of the formula in this other answer A little longer, but can be constrained to a limited array (not whole columns): where the order does not matter. Where it does: answered Sep 27, 2017 at 1:32
pnutspnuts 17k5 gold badges50 silver badges102 bronze badges 2 Now that Google Sheets has the This will work with whole-column ranges, although there could be performance issues with extremely large spreadsheets: The answered Aug 7, 2021 at 7:43
When working with data in Google Sheets, you may need to combine some specific cells (or combine cells in two columns). There are multiple ways you can do this using formula in Google Sheets. In this tutorial, I will show you how to combine cells in Google Sheets using a few simple formulas. So let’s get started!
A Step-By-Step Guide to Combine Columns In Google Sheets (2 Methods)We have several examples of formulas that combine cells and columns and the methods you can use them remains largely the same, so let’s take a look at the steps you can take. Either method for Google Sheets to combine columns into one will yield correct results for these formulas, it boils down to personal preference. Using the Fill Handle
Using an ARRAYFORMULAYou can use an ARRAYFORMULA instead
of the fill handle to choose the cells or columns to combine. ARRAYFORMULAS apply arrays to non array functions. To use them you simply have to wrap the formula with =ARRAYFORMULA() =ARRAYFORMULA(A2:A8&B2:B8) As you can see the exact range is specified rather than an individual row. Combine Cells Without Any SeparatorGoogle Sheets has some really simple formulas that you can use to combine cells and columns. Suppose you have a dataset as shown below and you want to combine columns A and B and get the result in column C. Below is the formula that will do this: =A2&B2 The above formula uses the ampersand operator (&) to combine values in cell A2 and B2. You can also use the below CONCATENATE formula to combine the two cells in Google Sheets: =CONCATENATE(A2,B2) The above CONCATENATE formula simply takes the values that you want to combine and gives you the result. Combine Cells With Separator (Comma/Space)In many cases, you would want to combine cells but have a separator in between. This could be a space character, a comma or a dash. Again, this can be done with simple formulas by specifying the separator when you’re combining cells. Suppose you have the dataset as shown below and you want to combine the cells in columns A and B with a dash in between. Below is the formula that will do this: =A2&"-"&B2 In the above formula, I combine three elements, cell A2, the separator (a dash which needs to be in double-quotes), and cell B2. You can also do the same with the CONCATENATE formula where you need to specify the separator as well within the formula (in double-quotes): =CONCATENATE(A2,"-",B2) While I have covered an example where I am combining only two cells, you can use the same logic when combining multiple cells Combining Cells in Google Sheets Separated With Line BreaksIn some cases, you may need to combine cells but have a line break in between the content of each cell. For example, suppose you have the address data as shown below where each element of the address (name, street, city, and state) is in a different cell and you want to combine these. But instead of having it as one long text string, you want each of these in a separate line in the same cell. This can also be done with a simple formula where you will have to use the line feed character as the separator. Below is the formula that will combine the cells while also inserting a line break between each cell’s content. =A2&char(10)&B2&char(10)&C2&char(10)&D2 Although the above formula would insert the line break, you would only see it when you have the wrap text enabled in the cells. To enable Wrap text, click on Format option in the menu, and then click on Text wrapping –> Wrap The above formula uses the CHAR(10) formula to get the line feed character as a part of the result. You can also get the same result using the following TEXTJOIN formula: =TEXTJOIN(CHAR(10),,A2:D2) The TEXTJOIN formula can take an array of cells and combine all these while having the specified delimiter/separator in between all the cells. Use Google Sheets to Combine Cells with Text and Numbers (Or Text and Dates)One area where you may face a challenge is when combining cells that contain text as well as numbers – especially when numbers are formatted in a specific way. For example, suppose I have the below dataset where I have text data (Department) in column A and number data (Expense) in column B. Also, the expense data in column B is formatted to show percentage value with two decimal points. Let me combine these two cells using a simple ampersand (with a dash as a separator) using the below formula: =A2&" - "&B2 Do you see the problem? The numeric data is shown in decimal points and the formatting has gone away. This happens as Google Sheets stores numbers in the cell (in the back-end) in a specific way and you can choose to format and show these numbers differently in the cell. For example, when I format the cell to show the value as 42.6%, in the backend it’s stored as 0.426. Both these values are the same, it’s just the way these are displayed in the cell will depend on the formatting of the cell. So when you combine these cells (ones that have numbers formatted in a certain way), the formatting goes away. But what if you want to keep the formatting? Then you need to modify the formula slightly and use another function in it – the TEXT function. In the above example, if I want to show the number as a percentage with two decimals, I can use the below formula: =A2&" - "&TEXT(B2,"#.00%") In the above formula, I have used the TEXT function to first convert the number into a text with a specific format. And this format is also specified within the formula (in double-quotes). This ensures that you see the data the same way as you see in the cells that you have combined. If you want, you can also change the format within the formula to show the number in a different format. Below is another example where I have the names in column A and their date of birth in column B. Below is the formula that will combine these two cells (with a dash as a separator). A2&" - "&B2 As you can see, the date gets converted into the underlying number, which is not what you would want when you expect a date of birth value instead. Below is the formula that can get this sorted: =A2&" - "&TEXT(B2,"dd-mmm-yyy") The above formula combines the two cells but also uses the TEXT formula to specify how the date should look like. Note that here you can change the format and make the date display the way you want. For example, if I want to show the full month name, I would use the below formula; =A2&" - "&TEXT(B2,"dd-mmmm-yyy") Or if I want to show the date in the October 12, 1985 format, I can use the below formula: =A2&" - "&TEXT(B2,"mmmm dd, yyy") Using the TEXT formula makes sure your number data is presented in a way you want, instead of the underlying value only. How to Combine Cells in Google Sheets Using the UNIQUE FunctionUnique is a native array compatible formula, so you don’t need to use an ARRAYFORMULA to use it to combine cells. Instead, you could use the following syntax: =UNIQUE({Array_1;Array_2}) Check out the below example to see it in action: As you can see column C has the values of column A and B. Using unique also removes any duplicates which may be handy in many situations. So these are some of the ways you can use to combine cells in Google Sheets. In case you have to combine columns (one or more), you can use similar formulas and apply them for the entire columns. Read more: How to Use the UNIQUE Function. The Google Sheets Combine Multiple Columns Into One FAQHow Do I Combine Cells in Google Sheets?Google Sheets can combine text from two cells with several different formulas, but the simplest is just putting the & symbol between two cell references in an empty cell, like so: =A2&B2 How Do I Combine Data From 3 Cells Into 1?You can use any of the above methods to combine data from 3 cells into one by adding third cell reference. For example, =A2&B2&C2 would combine those cells. How Do You Use CONCATENATE?CONCATENATE combines cells and lets you choose a seperator, you just have to provide the cell references like in the following example: =CONCATENATE(A2,"-",B2) This would provide the date from A2 and B2 with a dash in between. If You Found This Tutrialon How to Combine Cells in Google Sheets Here Are Some Other Google Sheets Tutorials You May Like:
|
