Concatenate range (without UDF)

  • Thread starter Thread starter Greg Lovern
  • Start date Start date
G

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
 
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.
 
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
 
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
 
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
 
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.
 
Back
Top