concatenate cells by a given range

  • Thread starter Thread starter Twan Kennis
  • Start date Start date
T

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
 
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 ...?!
 
Sorry, I misread that part.

Twan said:
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 ...?!
Click to expand...
 
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.
 
Back
Top