1

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 at 4:12
1

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.

  • 2
    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 at 14:41
1

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 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 at 0:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.