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

SOLUTION

Robert Schutt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

redmondb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

scsyme

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve_Brady

ASKER

Thanks for the responses and the sample file.

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,"""",""),A1,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?" :))

SOLUTION

scsyme

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve_Brady

ASKER

Thank you for the great responses.