concatenate cells by a given range

[Twan Kennis]

Is there a way to concatenate cell-vaues by a given range without using
VBA of external add-ins, like:

=CONCAT(A1:A3000)

instead of

=CONCATENATE(A1;A2;A3; ................................;A3000)

Thanx in advance.

Greets, Twan

 

[Dave Peterson]

J.E. McGimpsey has an =MultiCat() that works like =sum() works with numbers:
http://mcgimpsey.com/excel/udfs/multicat.html

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Twan Kennis]

Unfortenately MultiCat() is NOT a standard Excel-function, which I asked
for.
By using VBA it's peanuts to create such a function, but without, its a
brain-breaker, or even impossible ...?!

 

[Dave Peterson]

Sorry, I misread that part.

Unfortenately MultiCat() is NOT a standard Excel-function, which I asked
for.
By using VBA it's peanuts to create such a function, but without, its a
brain-breaker, or even impossible ...?!

 

[Herbert Seidenberg]

Not very practical, but just for fun and
to show that it can be done without VBA...
This simplified example concatenates the 10 numbers in set1.

set1 acc
1 1 2 3 4 5 6 7 8 9 10
2
3
4
5
6
7
8
9
10

control
3
inc
11

All ranges are named with the headers indicated.
Create three option buttons from Forms,
label them Reset, Set and Lock and
link them to the control cell.
Click the Reset button, so control shows 1.
Enter these formulas in acc and inc respectively:
=IF(control=1,INDEX(set1,1),IF(control=2,acc&" "&INDEX(set1,inc),acc))
=IF(control=1,2,IF(control=2,inc+1,inc))
Tools > Options > Calculation > Max Iterations > 9
Click Set, then Lock.