Split text in cell without common delimiter

[ChizDee]

i There,
I am not sure if what I am wanting to do is possible. I have looked through the internet but unable to find a solution but it might be that I am asking it wrong.
I have a cell containing multiple notes, each being with a date DD/MM/YYYY. I would like to split the text at the date each time. Some cells have 2 dates some have 10 dates. I have attached a screenshot of the cell that I would like split and in the first row highlighted the dates where I would like it split.
Hope that all makes sense.
Many thanks

 

[Peter_SSs]

Welcome to the MrExcel board!

Not sure how robust this would be with your data, but give it a try.
Note that column G is left blank.

For the future , I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

ChizDee.xlsm
	F	G	H	I	J
1
2	29/03/2022 10:00 - text 05/04/2022 other text		29/03/2022 10:00 - text	05/04/2022 other text
Sheet1

Cell Formulas
Range	Formula
H2:J2	H2	=LET(tj,TEXTJOIN(" ",,$G2:G2),IFERROR(TRIM(SUBSTITUTE(LEFT($F2,SEARCH("??/??/???? ",$F2&"11/11/1111 ",LEN(tj)+10)-1),tj,"")),""))

 

[ChizDee]

Oh my goodness thank you so much!
And yes I will definitely do the XL2BB in future. Thank you for the heads up.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

If you strike any problems with that, another option would be to employ a user-defined function like below. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across and down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function SplitOnDate(s As String, num As Long) As String
  Dim RX As Object, M As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "\d\d\/\d\d\/\d\d\d\d.*?(?=(\d\d\/\d\d\/\d\d\d\d)|$)"
  Set M = RX.Execute(s)
  If num <= M.Count Then SplitOnDate = M.Item(num - 1)
End Function

ChizDee.xlsm
	F	G	H	I
1
2	29/03/2022 10:00 - text 05/04/2022 other text	29/03/2022 10:00 - text	05/04/2022 other text
Sheet1 (2)

Cell Formulas
Range	Formula
G2:I2	G2	=SplitOnDate($F2,COLUMNS($G:G))