concatenating stagered strings

swendingo

Board Regular
Joined
Sep 11, 2003
Messages
90
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
UVWX
211/18/05
22 
23 
24 
25 
26 
27 
2812/12/04
29 
3012/11/04
31 
32 
33 
34 
35 
36 
37 
3811/30/04
39 
4011/29/04
Sheet2
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
try the UDF
Code:
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

Board Regular
Joined
Sep 11, 2003
Messages
90
How about reversing the order of how they appear?

:biggrin:

Thank you so much for the help!
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
Code:
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
 

Forum statistics

Threads
1,147,690
Messages
5,742,647
Members
423,746
Latest member
Joaogomes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top