Steve_Brady

asked on

# Concatenate in Excel without entering every cell reference

Hello,

In Excel, is there a way to concatenate a large number of cells by defining a range rather than including each individual cell reference?

For example, suppose you want to concatenate the range, A1:A99. To my knowledge, there are two ways to do that but both involve including every single cell reference in the formula:

=A1&A2&A3&A4&......&A99

=CONCATENATE(A1,A2,A3,A4,......,A99)

It sure would be helpful to have some function of the form:

=SOMEFXN(A1:A99)

which gives the same result.

Is such a function available?

The obvious follow-up, if such a function does exist, is whether or not there is a way to define a connector. For example, suppose you want the same range above to be concatenated but include a connector such as an underscore:

=A1&"_"&A2&"_"&A3&"_"&A4&"_"&......&"_"&A99

Is it possible to do that?

Thanks

In Excel, is there a way to concatenate a large number of cells by defining a range rather than including each individual cell reference?

For example, suppose you want to concatenate the range, A1:A99. To my knowledge, there are two ways to do that but both involve including every single cell reference in the formula:

=A1&A2&A3&A4&......&A99

=CONCATENATE(A1,A2,A3,A4,.

It sure would be helpful to have some function of the form:

=SOMEFXN(A1:A99)

which gives the same result.

Is such a function available?

The obvious follow-up, if such a function does exist, is whether or not there is a way to define a connector. For example, suppose you want the same range above to be concatenated but include a connector such as an underscore:

=A1&"_"&A2&"_"&A3&"_"&A4&"

Is it possible to do that?

Thanks

SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**ASKER

Thank you for the great responses.

ASKER

I attempted to get my head around what is going on in the file you posted but I'm a bit confused. For example, the following formula is present in cell B1:

=CONCATENATE(IF($D$1,"""",

Can you explain the "IF" functions? I don't understand how the criteria (first argument) for an "IF" question can simply be a cell reference. Usually "IF" criteria contain something like:

$D$1=8

$D$1>5

$D$1=""

$D$1<>""

It reminds me of one of several nonsense questions I heard years ago from a comedian:

Question: "What is the difference between a duck?"

Thanks

FYE, some of the others were:

"Do you walk to school or carry your lunch?"

"Is it faster to Chicago or by bus?"

"Is a colder in the winter or in the mountains?" :))