On Mon, 26 Nov 2012 19:34:03 +0000, jspring0033 wrote:


I am having an issue concatenating a large amount of data from a row.

What I am trying to do is merge all of the data in each cell into one
cell in the row. The data in the cells is a date format. Does anyone
know a formula that will do this for me?

The range is 365 cells in the row some cells have data and some do not
but they look similar to the below with 1 date in each cell.
11/25/12| |11/23/12| |10/25/12|

I want it to return this:
11/25/12, 11/23/12, 10/25/12 all in one cell "V2" for all the data in
row 2 and so on. . If there is not data in the cell then it goes to the
next. So if I had 365 cells with data in the row I want it to return
365 dates seperated by a comma in 1 cell.

Any help would be much appreciated! I have attached a picture!


+-------------------------------------------------------------------+
|Filename: Excel.JPG |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=699|
+-------------------------------------------------------------------+

I would suggest a User Defined Function (UDF)

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=ConcatRange(A1:NA1)

in some cell.


=======================================
Option Explicit
Function ConcatRange(rg As Range) As String
Dim c As Range
Dim s As String

For Each c In rg
If c < "" Then s = s & ", " & c.Text
Next c
ConcatRange = Mid(s, 2)
End Function
==========================