concatenate cells by a given range
 

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

concatenate cells by a given range
 

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 wrote:

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

concatenate cells by a given range
 

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" wrote in message
...
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 wrote:

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

concatenate cells by a given range
 

Sorry, I misread that part.

Twan Kennis wrote:

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" wrote in message
...
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 wrote:

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

--

Dave Peterson

concatenate cells by a given range
 

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.