Concatenate range (without UDF)

[Greg Lovern]

Is it possible to concatenate a range using only Excel's built-in
worksheet functions?

Yes, I know it can be done in a UDF, and I can do that. I'm just
wondering if it's possible to do it in Excel's built-in worksheet
functions.


Thanks,

Greg

 

[Mike H]

Greg,

Probably yes but you may need to explain more. Try

=A1&B1&C1 etc

Mike

 

[T. Valko]

Yes, but you have to reference each cell in the range individually.

=A1&B1&C1
=CONCATENATE(A1,B1,C1)

If you mean something like:

=CONCATENATE(A1:C1)

Then, no, can't be done with only built-in functions.

 

[Greg Lovern]

Thanks, I did mean concatenating a range reference, because the range
is dynamic, so I can't effectively reference individual cells.

I don't know why Microsoft lets things like this go unimproved version
after version after version. A similar annoyance is that AND and OR
are not array-aware.


Greg

 

[T. Valko]

You're preaching to the choir!

--
Biff
Microsoft Excel MVP


Thanks, I did mean concatenating a range reference, because the range
is dynamic, so I can't effectively reference individual cells.

I don't know why Microsoft lets things like this go unimproved version
after version after version. A similar annoyance is that AND and OR
are not array-aware.


Greg

 

[Greg Lovern]

Are there other functions besides CONCATENATE, AND, & OR that are not
array-aware but also are not scalar (returning a single result for
multiple inputs, such as SUM, MAX, COUNT, etc., and therefore would
not benefit from being array-aware)? Is there a list of them out there
somewhere?

Greg

 

[T. Valko]

I guess you want to know which functions don't work with arrays. I don't
know of a list.

RANK

There's a lot of them in the Analysis ToolPak add-in. Here's a few:

WEEKNUM
ISODD
ISEVEN
NETWORKDAYS

Just a thought on the general subject of functions...

It would seem to me that developing functions is probably the easiest thing
to do in terms of the Excel application development as a whole. Yet, new
and/or improved functions are rare and few when new versions of Excel are
released. Each new release of Excel seems to be geared more towards data
presentation than data analysis.