concatenating stagered strings

[swendingo]

I have a column with dates in it. The dates are generated by a formula and there is no pattern to the location of the dates. I am trying to write a formula (or function) that will concatenate the dates for me (separated by a coma)

EX. (1/18/05, 12/12/04, 12/11/04)

(the data will be distributed differently every time)

Does anyone have any tips for me?

absentCount.xls
	U	V	W	X
21	1/18/05
22
23
24
25
26
27
28	12/12/04
29
30	12/11/04
31
32
33
34
35
36
37
38	11/30/04
39
40	11/29/04
Sheet2

 

[jindon]

Hi
try the UDF

Public Function con_date(rng As Range) As String
    Dim r As Range, x As String
        For Each r In rng
            If IsDate(r) Then
                x = x & r.Text & ", "
            End If
        Next
    x = Left(x, Len(x) - 2)
    con_date = x
End Function

use:
=con_date(yourRange)
it will pick up date within the range

hope this helps
jindon

 

[swendingo]

that is great ... Thank You

 

[swendingo]

How about reversing the order of how they appear?



Thank you so much for the help!

 

[jindon]

Hi swendingo

it is easy just change one line

x = x & r.Text & ", "
to
x= r.Text & x

the following code I changed a bit

Public Function swendingo(rng As Range, spr As String) As String
    Dim r As Range, x As String
        For Each r In rng
            If IsDate(r) Then
                x = r.Text & spr & x
            End If
        Next
    x = Left(x, Len(x) - Len(spr))
    swendingo = x
End Function

use:
=swendingo(YourRange,"separate string")
will display with separate string at the end of each value e.g. " - "

rgds,
jindon