how to split data into columns between underscore and and dash when there can be more than

[karynd]

AUSP Policy ID: 320764 Eff: 29/09/2021

___10 Jan 2022 - Daniel Joiner WA___
Status changed to Complete ___11 Jan 2022 - Jo Atwell___

am trying to split the above date and I want the date and the name in a new column - the data could exist of more than just 2 dates and names it could be 20 or 10 or just 1 but I just want to be able to split out based on any data in between the ___ and the - and the 2nd ___ into its own separate column as per below

is there a formula that would work nicely for this ?
appreciate your help
I originally asked the question with the brackets and got an amazing response that worked by Bosco =TRANSPOSE(FILTERXML("<a>"&SUBSTITUTE(SUBSTITUTE(A1,"[","<b>"),"]","</b>")&"</a>","//b"))
but the report that is run out of a system I don't control and I have been told cant be changed to brackets around the names and date as i had hope and it currently comes out with the underscore ( i think that is what it is seems to be 3 underscores but I am not sure) and the dash in between the date and name.
I tried adapting the formula but I could not get it to work
is there a formula that would split that up?
really appreciate the assistance
cheers




cheers
karyn

AUSP Policy ID: 334980 Eff: 20/04/2022
Assigned user changed to Joan Lim
___06 Jul 2022 - Joan Lim___
Status changed to In Progress. 6/7/2022 JL spoke to Beau and he advised client paid them 27/4 and they remitted payment to us on 16/6. He will ask his accounts to send us the remittance advice.
___06 Jul 2022 - Joan Lim___
7/7 Mei emailed broker re premium discrepancy - waiting to hear from them.
___11 Jul 2022 - Joan Lim___
29/7 - emailed reminder to Beau for revised closing
___29 Jul 2022 - Joan Lim___
Assigned user changed to Neha Upadhyay. Neha please allocate the premium sitting in UA cash ID 114780. The client has taken back the $45.98 overpayment.
___10 Aug 2022 - Joan Lim___

___12 Aug 2022 - Nancy Mattos___

 

[pecoflyer]

Hi, please post a sample sheet (no pics please) with some data and manually mocked up results

 

[bosco_yip]

Try,

In B14. formula copied across right:

=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($A1,"2022 - ","___"),"___","</b><b>")&"</b></a>","//b["&(COLUMN(A$1)+INT((COLUMN(A$1)-1)/2)+1)&"]"),"")

 

[karynd]

Try,

In B14. formula copied across right:

=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($A1,"2022 - ","___"),"___","</b><b>")&"</b></a>","//b["&(COLUMN(A$1)+INT((COLUMN(A$1)-1)/2)+1)&"]"),"")

View attachment 81124

OMG Bosco you are absolutely a genius - you really are an excel ninja - thank you thank you thank you
it works a treat