How to concatenate range of cells in a row

Question

I have a simple Excel spreadsheet with a single row and 4 columns. I'm trying to concatenate 3 cells in the row together using a range instead of specifying all 3 cells explicitly (for times where I need to concatenate several cells).

The data is as follows:

[A1] 5
[B1] 6
[C1] 7
[D1] =CONCATENATE(A1:C1)

The result I get in D1 is #VALUE instead of what I'm looking for, which would be 567.

Is there a way to accomplish this using a range?

score 1 · Accepted Answer · answered Jan 08 '19 at 23:44

1

If you have Office 365:

=CONCAT(A1:C1)

If not

=A1 & B1 & C1

or

=CONCATENATE(A1,B1,C1)

answered Jan 08 '19 at 23:44

Scott Craner

148,073
10
49
81

I'm using Excel 2016, not Office 365. Explicitly specifying the cells works, but it becomes a pain when there's 20 or 30 cells to concatenate. – Emily Samantha Heiner Jan 08 '19 at 23:49
Without Office 365, the only way with a formula is to do the individual cells, or a helper column. – Scott Craner Jan 08 '19 at 23:52

score 0 · Answer 2 · answered Dec 24 '22 at 21:16

A helper column can gradually build the string, by concatenating above row with the next value.

  A  B      B (formula)
1 h  h      =A1        Initial value
2 e  he     =B1&A2     Concatenate above cell with next value
3 l  hel    =B2&A3     Copy formula downwards, it should auto-increment.
4 l  hell   =B3&A4
5 o  hello  =B4&A5

(Similar question: Concatenate range in Excel with formulas)

How to concatenate range of cells in a row

2 Answers2