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!

*Bump* Anyone have ideas?

with vba ?

otherwise
=TEXT(A1,"mm/dd/yy")&", "&TEXT(B1,"mm/dd/yy")&", "&...

--
isabelle



Le 2012-11-28 14:41, jspring0033 a écrit :
jspring0033;1607634 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!

*Bump* Anyone have ideas?


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+

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.

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.

Following your picture, I put dates in Sheet1!A1:U20.

I used Sheet2 as a workspace and put the following formulas there.

In Sheet2!A1:
=IF(Sheet1!A1="","",TEXT(Sheet1!A1,"mm/dd/yy"))
and copied it down to A20.

In Sheet2!B1:
=A1&IF(Sheet1!B1="","",IF(A1="","",", ")&TEXT(Sheet1!B1,"mm/dd/yy"))
and copied it down to B20.

Then selected B1:B20 and copied it all the way to U1:U20.

Finally, I returned to Sheet1 and put this in Sheet1!V1:
=Sheet2!U1
and copied down to V20.

Seems to work, though it's not especially elegant.

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
==========================

Thank you very much! This worked nicely with what I wanted to accomplish!

I am going to try this as well for future projects, I am just not the best at VBA. Thanks!