Parse dates into comma delimit values

[GreyFox8991]

Greetings Excel Community,

I have a set of data and i am wondering if there is code or formula that can be created to parse these values into comma delimited dates. The dates are combined together to make it look as a long string, however i am attempting to create the end result under the "Formatted dates" column. Any insight would be greatly appreciated.

Book1
	A	B
1	Unformatted Dates	Formatted Dates
2	01/01/2022/01/05/202206/17/202207/28/2022	01/01/2022,01/05/2022,06/17/2022,07/28/2022
3	01/01/2022/01/04/202204/14/2022	01/01/2022,01/04/2022,04/14/2022
4	01/01/2022/01/05/202201/25/2022/01/30/202202/27/2022	01/01/2022,01/05/2022,01/25/2022,01/30/2022,02/27/2022
Sheet1

 

[DanteAmor]

Hi @GreyFox8991, I hope you are well.

If the data is like in your minisheet, then the results will be in cell B2 and down.
Try this macro:

Sub Macro_ParseDates()
  Dim f1 As String, f2 As String
  Dim j As Long
  Dim c As Range
 
  For Each c In Range("A2", Range("A" & Rows.Count).End(3))
    f1 = Left(c.Value, 10)
    f2 = Mid(c.Value, 12)
    For j = 1 To Len(f2)
      f1 = f1 & "," & Mid(f2, j, 10)
      j = j + 9
    Next
    c.Offset(0, 1).Value = f1
  Next
End Sub

HOW TO INSTALL MACROs
------------------------------------

If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Macro_ParseDates) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.​

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[GreyFox8991]

Hi Dante,

Thank you for your reply... I have tested the code and I see the following results.... seems like there is a comma placed right at the end of the year value eX 07/14/2022 as 07/14/202,2.. Pasting a mini **** below for your reference if needed. I tried to highlight the comma values as red but seems like it is not visible with minisheet.

EDIT Epi_Sharecare_Chase Upload_09252023.xlsx
	A	B
1	01/01/2022/01/04/202204/14/202207/23/2022/07/25/202212/02/2022/12/03/2022	01/01/2022,01/04/2022,04/14/2022,07/23/2022,/07/25/202,212/02/202,2/12/03/20,22
Sheet2

 

[DanteAmor]

01/01/2022 / 01/04/2022 04/14/2022 07/23/2022 / 07/25/2022 12/02/2022 / 12/03/2022

01/01/2022 / 01/05/2022 01/25/2022 / 01/30/2022 02/27/2022

I hadn't noticed that you have the diagonal as a date separator in some places.

Try the following macro:

Sub Macro_ParseDates()
  Dim f1 As String, f2 As String
  Dim j As Long
  Dim c As Range
 
  For Each c In Range("A2", Range("A" & Rows.Count).End(3))
    f1 = Left(c.Value, 10)
    f2 = Mid(c.Value, 11)
    For j = 1 To Len(f2)
      If Mid(f2, j, 1) <> "/" Then
        f1 = f1 & "," & Mid(f2, j, 10)
        j = j + 9
      End If
    Next
    c.Offset(0, 1).Value = f1
  Next
End Sub

Dante Amor
	A	B
1	Unformatted Dates	Formatted Dates
2	01/01/2022/01/04/202204/14/202207/23/2022/07/25/202212/02/2022/12/03/2022	01/01/2022,01/04/2022,04/14/2022,07/23/2022,07/25/2022,12/02/2022,12/03/2022
3	01/01/2022/01/04/202204/14/2022	01/01/2022,01/04/2022,04/14/2022
4	01/01/2022/01/05/202201/25/2022/01/30/202202/27/2022	01/01/2022,01/05/2022,01/25/2022,01/30/2022,02/27/2022
5	01/01/2022	01/01/2022
6	01/01/2022/01/05/2022	01/01/2022,01/05/2022
7	01/01/2022/01/04/202204/14/202207/23/2022/07/25/202212/02/2022/12/03/2022	01/01/2022,01/04/2022,04/14/2022,07/23/2022,07/25/2022,12/02/2022,12/03/2022
8	01/01/2022/01/05/202206/17/202207/28/2022	01/01/2022,01/05/2022,06/17/2022,07/28/2022
9	01/01/202201/01/2022	01/01/2022,01/01/2022
Hoja2

Regards
Dante Amor
--------------