How to extract the date between "and" rather than delimiter

[rachelbi]

Hi there, I would like to add public holiday in the calendar visual, yet I don't know how to extract the date properly between two dates which are joined by "and" instead of any delimiter, grateful to have some tips. Thank you.

[ryan_mayu · Accepted solution]

@rachelbi 

you can try split by "and" and to rows

[rachelbi]

Thanks @ryan_mayu  I tried your suggestion yet the date format e.g. "Sunday 1 January" cannot be formatted to 1/1/2023 (below capscreen). Wonder what should I do? Appreciated.

source: Public holidays | Recreation, sport and arts | Queensland Government (www.qld.gov.au) 

[ryan_mayu]

@rachelbi 

then you extracted text after space

then create a custom column