Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to concatenate a range?
To sum up a range I can use SUM(A1:A30) but cells A1:A30 contain text
(strings). The function CONCATENATE(A1:A30) does work here. Is there a simple way to that without having to use Range("A1") & Range("A2") & ..........Range("A30")? Thanks, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to concatenate a range?
Nathan,
I think you need to roll your own. Something like Function Concat(ParamArray V()) As String Dim Ndx As Long Dim R As Range Dim S As String For Ndx = LBound(V) To UBound(V) If TypeOf V(Ndx) Is Excel.Range Then For Each R In V(Ndx).Cells S = S & R.Text Next R Else S = S & Format(V(Ndx)) End If Next Ndx Concat = S End Function You can then call this from a worksheet as =Concat(A1:A10) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Nathan Gutman" wrote in message ... To sum up a range I can use SUM(A1:A30) but cells A1:A30 contain text (strings). The function CONCATENATE(A1:A30) does work here. Is there a simple way to that without having to use Range("A1") & Range("A2") & ..........Range("A30")? Thanks, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to concatenate a range?
Thanks Chip, I was afraid of that.
On Thu, 18 Dec 2003 10:50:48 -0600, "Chip Pearson" wrote: Nathan, I think you need to roll your own. Something like Function Concat(ParamArray V()) As String Dim Ndx As Long Dim R As Range Dim S As String For Ndx = LBound(V) To UBound(V) If TypeOf V(Ndx) Is Excel.Range Then For Each R In V(Ndx).Cells S = S & R.Text Next R Else S = S & Format(V(Ndx)) End If Next Ndx Concat = S End Function You can then call this from a worksheet as =Concat(A1:A10) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate range (without UDF) | Excel Worksheet Functions | |||
Concatenate with date range | Excel Discussion (Misc queries) | |||
function to concatenate range | Excel Discussion (Misc queries) | |||
concatenate a range function | Excel Worksheet Functions | |||
Concatenate a range | Excel Worksheet Functions |