*Issue* Concatenate dates in a large range of cells in a row.

[jspring0033]

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 on
cell in the row. The data in the cells is a date format. Does anyon
know a formula that will do this for me?

The range is 365 cells in the row some cells have data and some do no
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 i
row 2 and so on. . If there is not data in the cell then it goes to th
next. So if I had 365 cells with data in the row I want it to retur
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
+-------------------------------------------------------------------

 

[jspring0033]

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 on
cell in the row. The data in the cells is a date format. Does anyon
know a formula that will do this for me?

The range is 365 cells in the row some cells have data and some do no
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 i
row 2 and so on. . If there is not data in the cell then it goes to th
next. So if I had 365 cells with data in the row I want it to retur
365 dates seperated by a comma in 1 cell.

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

*Bump* Anyone have ideas

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

 

[isabelle]

with vba ?

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

--
isabelle



Le 2012-11-28 14:41, jspring0033 a écrit :

 

[zvkmpw]

I am having an issue concatenating a large amount of data from a row.
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.

 

[Ron Rosenfeld]

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

 

[jspring0033]

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.

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

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