Concatenate a range
How do you concatenate an entire range at once?
=CONCATENATE(A1:A10) entered as array still only returns the first element, A1. There has to be something better than - =CONCATENATE(A1,A2,A3,A4,A5,...A10) Thanks |
Use a simple UDF like this:
Function Concatall(rng As Range) As String Dim cell As Range For Each cell In rng Concatall = Concatall & cell.Text Next End Function --- Call as =Concatall(A1:A10) HTH Jason Atlanta, GA -----Original Message----- How do you concatenate an entire range at once? =CONCATENATE(A1:A10) entered as array still only returns the first element, A1. There has to be something better than - =CONCATENATE(A1,A2,A3,A4,A5,...A10) Thanks . |
-----Original Message----- Use a simple UDF like this: Function Concatall(rng As Range) As String Dim cell As Range For Each cell In rng Concatall = Concatall & cell.Text Next End Function --- Call as =Concatall(A1:A10) HTH Jason Atlanta, GA -----Original Message----- How do you concatenate an entire range at once? =CONCATENATE(A1:A10) entered as array still only returns the first element, A1. There has to be something better than - =CONCATENATE(A1,A2,A3,A4,A5,...A10) Thanks . . That works. Thank you. |
Jason Morin wrote...
Use a simple UDF like this: Function Concatall(rng As Range) As String Dim cell As Range For Each cell In rng Concatall = Concatall & cell.Text Next End Function .... Or generalize, Function mcat(ParamArray s()) As String 'Copyright (C) 2002, Harlan Grove 'This is free software. It's use in derivative works is covered 'under the terms of the Free Software Foundation's GPL. See 'http://www.gnu.org/copyleft/gpl.html '------------------------------------ 'string concatenation analog to SUM Dim r As Range, x As Variant, y As Variant For Each x In s If TypeOf x Is Range Then For Each r In x.Cells mcat = mcat & r.Value Next r ElseIf IsArray(x) Then For Each y In x mcat = mcat & IIf(IsArray(y), mcat(y), y) Next y Else mcat = mcat & x End If Next x End Function which allows expressions like =mcat("hi",(A1:A4,A6:D6,C2),"foo",A7:F9) |
All times are GMT +1. The time now is 06:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com