Concatenate function should accept cell-ranges

[johndog]

Why should a user have to specify:

=Concatenate(A1, A2, A3, B1, B2, B3)
?

Why not simply:

=Concatenate(A1:B3)
?

This would make it *so* much easier to abstract away certain changes, to
reverse operations such as "text-to-columns", and to avoid making errors.
I've had to concatenate a series of more than 20 cells before, and it really
gets tedious after only the 3rd one. If I had to insert a column or row to
the concatenation, I had to update the function again, and if I made a
mistake it was hard to detect, etc etc.



----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...0-82b66f0bebd9&dg=microsoft.public.excel.misc

 

[David Biddulph]

Why should a user have to specify:

=Concatenate(A1, A2, A3, B1, B2, B3)
?

Why not simply:

=Concatenate(A1:B3)
?

This would make it *so* much easier to abstract away certain changes, to
reverse operations such as "text-to-columns", and to avoid making errors.
I've had to concatenate a series of more than 20 cells before, and it
really
gets tedious after only the 3rd one. If I had to insert a column or row
to
the concatenation, I had to update the function again, and if I made a
mistake it was hard to detect, etc etc.

Are you happy for Excel to guess whether when you said (A1:B3) you intended
(A1, A2, A3, B1, B2, B3)
or
(A1, B1, A2, B2, A3, B3)?

 

[johndog]

Are you happy for Excel to guess whether when you said (A1:B3) you intended
(A1, A2, A3, B1, B2, B3)
or
(A1, B1, A2, B2, A3, B3)?

Good rhetorical question. Obviously the answer is no.

But Excel doesn't need to guess, it can simply lay down some rules. These
are all options, for instance:

1) One row or one column only
2) Left to right, top down only
3) Allow a parameter to select between left-right-top-down,
right-left-top-down, bottum-up-right-left, bottum-up-left-right (though this
would probably need another function altogether)

Dependency on ordering of cells in ranges is certainly precedented. For
instance, many of the search functions will assume the range is sorted. But
in this case, the behavior I'm suggesting is not required, and if the user
needs to combine their cells in a specific non-orderly way, then ranges
probably aren't an interesting solution to their problem. They can simply
continue to do it the old way. However, in the common "inverse of text to
columns" scenario discussed in the Excel's help, the issue of guessing which
order excel will do things in shouldn't even come into question; the output
should simply be the most intuitive, which is to say that it should loosely
correspond to the opposite behavior of "text to columns", whose behavior is
also intuitive.

Combining rows *and* columns goes beyond "opposite behavior" of
text-to-columns, but it could be an added benefit. I actually think a fixed
"left-right-top-down" behavior is rather reasonable. Geometrically,
left-to-right maps in order into the output string, and since the output
string reads left-right, it follows that top-to-bottom ordering would get a
lower priority.

 

[Pete_UK]

If the range was restricted to a 1-D array, then the user could
specify:

=CONCATENATE(A1:A3,B1:B3)
or
=CONCATENATE(A1:B1,A2:B2,A3:B3)

though there is not much benefit in the second one over what we
currently have.

Often, though, when you are recombining columns, you want to put some
delimiter between them, such as:

=A1&","&A2&","&A3 etc.,

so there wouldn't be much advantage in accepting cell ranges in this
instance.

Pete