Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Twan Kennis
 
Posts: n/a
Default 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


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Peterson
 
Posts: n/a
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Twan Kennis
 
Posts: n/a
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Peterson
 
Posts: n/a
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg
 
Posts: n/a
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I concatenate two currency cells to show a price range? nevermore627 Excel Worksheet Functions 5 June 30th 06 05:03 PM
Display first, second, etc Nonblank Cells in a Range Jeremy N. Excel Worksheet Functions 12 September 25th 05 01:47 PM
Counting empty cells within a range of cells Rosehill - ExcelForums.com New Users to Excel 2 May 2nd 05 08:53 AM
Counting empty cells within a range of cells Rosehill - ExcelForums.com New Users to Excel 0 April 7th 05 12:47 AM
Count cells in one range based on parameters in another range dave roth Excel Worksheet Functions 2 March 29th 05 05:33 PM


All times are GMT +1. The time now is 02:36 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"