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

[karynd]

https://chandoo.org/forum/members/bosco_yip.12515/

back in sept 22 Bosco_yip you gave me an amazing formula and it worked a treat until the year changed to 2023

this was the amazing formulas but it is now producing the information and putting the date as 2023 and the very last one combining the date and name
=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($A1,"2022 - ","___"),"___","</b><b>")&"</b></a>","//b["&(COLUMN(A$1)+INT((COLUMN(A$1)-1)/2)+1)&"]"),"")

this is what the formula is providing now that we have changed to the year 2023 yet the 1st date should read 22/11/22 and the 6th date should be 13 jan 2023 but the name should then split into a 6th name
if there is not a date of 2023 the 6th date and name splits but the dates all say 2023 when they should say 2022 if they are the year of 2022

I am hoping Bosco you will know what I need to do to fix - I have tried playing with the formula but I cant get it to correct.
I am sure it is a simple fix but my brain just get my head around it
thanks heaps
cheers Karyn

1st date	1st name	2nd date	2nd name	3rd date	3rd name	4th date	4th name	5th date	5th name	6th date
22/11/2023​	Elise Pretto	22/11/2023​	Julie Clancy	1/12/2023​	Julie Clancy	8/12/2023​	Julie Clancy	23/12/2023​	Julie Clancy	13 Jan 2023 - Julie Clancy

AUSP Policy ID: 360279 Eff: 18/08/2022 Manya Wittingslow ___22 Nov 2022 - Elise Pretto___ Followed up broker - Dean Bowen is dealing with this now. ___22 Nov 2022 - Julie Clancy___ Spoke to Dean he has requested accounts details from CBN. ___01 Dec 2022 - Julie Clancy___ Corrections are being processed by the broker and the remaining amount will be taken back soon - unallocated listed undert the name of Boneo Maze. ___08 Dec 2022 - Julie Clancy___ Still on unallocated. ___23 Dec 2022 - Julie Clancy___ Checked again the broker hasn't finalised this yet - sent a follow up. ___13 Jan 2023 - Julie Clancy___

 

[bosco_yip]

You give us only one (1) source data and 11 formula results as per above.

In order avoid to any misunderstanding.

Please write down "What is your expected result?" for us to work.

Thanks

 

[p45cal]

In the attached is your sample data in msg#1 above in cell A8.
In cell B8 is the formula:

=DROP(TEXTSPLIT(SUBSTITUTE(TEXTJOIN(" - ",TRUE,CHOOSECOLS(TEXTSPLIT(A8,"___"&CHAR(10),CHAR(10)&"___"),1)),"___","")," - "),,1)

The formula in cell B4 is similar but includes the Policy ID in the first cell.
I hope you have a version of Excel which is sufficiently up to date.
I haven't figured out why the results of the original formula in cell B1 have all dates in 2023.
Link to previous thread: https://chandoo.org/forum/threads/h...d-and-dash-when-there-can-be-more-than.49141/

 

[karynd]

You give us only one (1) source data and 11 formula results as per above.

In order avoid to any misunderstanding.

Please write down "What is your expected result?" for us to work.

Thanks

thanks Bosco for having a look at this again

the previous thread where you designed this formula

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

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...

chandoo.org

so your formula broke up the info in the source splitting the date and name between the dash and underscore of the source
of course the data could at any time have more than 6 names and dates so be able to copy across and pick up as many as are in the source and there could be a mix of years in the data like this data showed with dates of 2022 and 2023

the expected result would be similar that the formula is currently supplying except pull in the correct dates of 2022 and the last column split out the date of 2023 and name in a separate column - so below is the expected result

is that the info you require?
thanks heaps

1st date	1st name	2nd date	2nd name	3rd date	3rd name	4th date	4th name	5th date	5th name	6th date	6th name
22/11/2022​	Elise Pretto	22/11/2022​	Julie Clancy	1/12/2022​	Julie Clancy	8/12/2022​	Julie Clancy	23/12/2022​	Julie Clancy	13/01/2023​	Julie Clancy

 

[karynd]

In the attached is your sample data in msg#1 above in cell A8.
In cell B8 is the formula:

=DROP(TEXTSPLIT(SUBSTITUTE(TEXTJOIN(" - ",TRUE,CHOOSECOLS(TEXTSPLIT(A8,"___"&CHAR(10),CHAR(10)&"___"),1)),"___","")," - "),,1)

The formula in cell B4 is similar but includes the Policy ID in the first cell.
I hope you have a version of Excel which is sufficiently up to date.
I haven't figured out why the results of the original formula in cell B1 have all dates in 2023.
Link to previous thread: https://chandoo.org/forum/threads/h...d-and-dash-when-there-can-be-more-than.49141/

thankyou so much for your response - i have confirmed my excel is the most upto date. that other formula did not work . the formula that Bosco has supplied had worked a treat until we had a mix of 2022 and 2023 dates
cheers

 

[bosco_yip]

Try,

In B2, revised formula (with highlighted red) copied across right:

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

 

[karynd]

Try,

In B2, revised formula (with highlighted red) copied across right:

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

View attachment 82553

Bosco_Yip to the rescue again - you are a superstar and exactly what I needed thank you so much

 

[bosco_yip]

Bosco_Yip to the rescue again - you are a superstar and exactly what I needed thank you so much

You are welcome.

Regards
Bosco