Extract Date-Different Formats

[pto160]

I would like to extract the date even though the formats could be slightly different. The format in the text is year-month-day. The day can be written as 02 for example or 2. Here is an example.

Book1
	A	B
1	Text	What I want
2	Café Nero-10-22-22-Paul Smith-Cash	10/22/2022
3	Café Nero-10-2-22-Paul Smith-Cash	10/2/222
4	Café Nero-10-02-22-Paul Smith-Cash	10/2/2022
5	Café Nero-03-02-22-Paul Smith-Cash	3/2/2022
6	Café Nero-03-2-22-Paul Smith-Cash	3/2/2022
Sheet3

 

[Eric W]

One possibility, assuming it immediately follows the first dash:

Book1
	A	B
1	Text	What I want
2	Café Nero-10-22-22-Paul Smith-Cash	10/22/2022
3	Café Nero-10-2-22-Paul Smith-Cash	10/2/2022
4	Café Nero-10-02-22-Paul Smith-Cash	10/2/2022
5	Café Nero-03-02-22-Paul Smith-Cash	3/2/2022
6	Café Nero-03-2-22-Paul Smith-Cash	3/2/2022
Sheet8

Cell Formulas
Range	Formula
B2:B6	B2	=AGGREGATE(14,6,MID(A2,FIND("-",A2)+1,{6,7,8,9,10})+0,1)

 

[pto160]

Eric W, thank you so much. This really works. Actually the text is year-month-day and the formula works. In some circumstances the place name could have a dash. The date always begin with a 4 digit year like 2022 or 2023 if that would help.

Book1
	A	B
1	Text	What I want
2	Café Nero-2022-10-22-Paul Smith-Cash	10/22/2022
3	Café Nero-2022-2-22-Paul Smith-Cash	2/22/2022
4	Café Nero-2022-02-22-Paul Smith-Cash	2/22/2022
5	Café Nero-2022-02-22-Paul Smith-Cash	2/22/2022
6	Café Nero-2022-2-22-Paul Smith-Cash	2/22/2022
7	Café-1 Nero-2022-2-22-Paul Smith-Cash	#NUM!
Sheet3

Cell Formulas
Range	Formula
B2:B7	B2	=AGGREGATE(14,6,MID(A2,FIND("-",A2)+1,{6,7,8,9,10})+0,1)

 

[Eric W]

If the date always begins with a year, specifically 20xx, then we can search for "-20" instead of just "-":

Book1
	A	B
1	Text	What I want
2	Café Nero-2022-10-22-Paul Smith-Cash	10/22/2022
3	Café Nero-2022-2-22-Paul Smith-Cash	2/22/2022
4	Café Nero-2022-02-22-Paul Smith-Cash	2/22/2022
5	Café Nero-2022-02-22-Paul Smith-Cash	2/22/2022
6	Café Nero-2022-2-22-Paul Smith-Cash	2/22/2022
7	Café-1 Nero-2022-2-22-Paul Smith-Cash	2/22/2022
8	Walmart-2023-11-8-John Doe-check	11/8/2023
9	Amazon-Smile-9-2000-12-31-Jane Roe-bitcoin	12/31/2000
Sheet10

Cell Formulas
Range	Formula
B2:B9	B2	=AGGREGATE(14,6,MID(A2,FIND("-20",A2)+1,{6,7,8,9,10})+0,1)

 

[pto160]

Thank you so much. This works great.

 

[Eric W]

Happy to help! Thanks for the update.