Extract date from text column and convert it to desired date format

[elmer57]

Hi - I'm running Office / Excel 365 in Windows 10. I have a text column that contains dates (in different formats) surrounded by other text. The column is free-form, meaning the date is mixed into the text column with no specific pattern. I would like to find the the date in the text column and copy it to another column using a specific column format. To complicate things, the date in the text column is sometimes formatted with periods, dashes or slashes and the date character length varies. The "source" column below is an example of my text column and the "Desired / expected result" column is what I'm hoping to get. Any help would be greatly appreciated.

Xl2bb code:

Extract example.xlsx
	A	B
1	Source	Desired / Expected result
2	South 1-18-22 video other text	2022-01-18
3	Brick stripe building 05.18.2023 other text	2023-05-18
4	tree cancel lawnmower 2/23/2019	2019-02-23
Sheet1

Thank you!

 

[Rick Rothstein]

As long as those three date formats are the only variations there are, this should work...

=TEXT(MAP(A2:A4,LAMBDA(a,MAX(MAP(TEXTSPLIT(SUBSTITUTE(a,".","/")," "),LAMBDA(d,IFERROR(0+d,0)))))),"yyyy-mm-dd")

 

[elmer57]

=TEXT(MAP(A2:A4,LAMBDA(a,MAX(MAP(TEXTSPLIT(SUBSTITUTE(a,".","/")," "),LAMBDA(d,IFERROR(0+d,0)))))),"yyyy-mm-dd")

Thank you sooooo much - that did it!!! I truly appreciate you helping me out!!!