# Combine columns where there is only one data cell per row in Google Sheets

I have Google Sheets data that looks like this:

    A | B | C
-----------
1 | a |   |
2 |   | b |
3 | c |   |
4 |   |   | d
5 |   | e |
6 | f |   |
7 |   |   | g
8 |   | h |


I need to combine this data into one column like this:

| D |
-----
a
b
c
d
e
f
g
h


For every row, there is only one data cell in Columns A, B, or C. Is it possible to do this using a Google Sheets formula?

Thank you all for your help.

• =A1&B1&C1, just copy down as many rows as you want; if you want to convert to values, use Copy/Paste Special>Paste values only. – Tedinoz Oct 8 '19 at 4:12

## 2 Answers

Data type : strings

use some concatenation. Try this in D1

=ArrayFormula(A:A&B:B&C:C)


Data type : value

If you have number data then include a VALUE() function like so:
=ARRAYFORMULA(VALUE(A:A & B:B & C:C))
Credit to @iandllnghm in the comments of this answer.

• This works perfect as is for string data. Thank you for the suggestion. For others' reference, if you have number data then include a VALUE() function like so: ARRAYFORMULA(VALUE(A:A & B:B & C:C)) – iandllnghm Oct 8 '19 at 14:41

We can use the OFFSET function in the following formulas:


+----+----+---+---+-----------------+
|  A |  A | B | C |       D         |
+----+----+---+---+-----------------+
|  1 | a  |   |   | =A1             |
|  2 |    | b |   | =OFFSET($A2,,1) | | 3 | | | c | =A3 | | 4 | | d | | =OFFSET($A4,,2) |
|  5 | e  |   |   | =OFFSET(\$A5,,1) |
|  6 |    | f |   |                 |
|  7 |    |   | g |                 |
|  8 |    | h |   |                 |
|  9 | i  |   |   |                 |
+----+----+---+---+-----------------+



We place them in the D column starting from cell D1
Since we have a repeating pattern we select and drag down cells D3-D5.

• There is not a repeating pattern to the data. Thank you, though. – iandllnghm Oct 8 '19 at 14:39
• "not a repeating pattern". It looked like it. In any case the answer by pjmg is more general and a better fit for you. Glad it worked out. – marikamitsos Oct 9 '19 at 0:54