CODEWITHSUNDEEP
excel-formula
Emily Samantha Heiner
Emily Samantha Heiner

Reputation: 121

How to concatenate range of cells in a row

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?

Upvotes: 1

Views: 913

Answers (2)

Pillowcase
Pillowcase

Reputation: 714

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)

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152505

If you have Office 365:

=CONCAT(A1:C1)

If not

=A1 & B1 & C1

or

=CONCATENATE(A1,B1,C1)

Upvotes: 1

Related Questions