Extract Date from Text

[larinda4]

Good morning,

I have this text, (and it changes but it'll always be annual. Feb-Jan, July-June, etc.) and I need to pull the starting date from the cell.

For the Period: May 01, 2021 to January 30, 2022

I currently have this formula but it'll only work for some of the months, not all. For example, it works for January but not for February/May.

=MID($A$5,SEARCH(": ",$A$5)+2,LEN($A$5)-SEARCH("to",$A$5))

I feel like I'm so close but I'm stumped.

 

[Kerryx]

maybe

Book1
	A	B	C	D
5	For the Period: May 01, 2021 to January 30, 2026			May 01, 2021
Sheet1

Cell Formulas
Range	Formula
D5	D5	=MID(A4,SEARCH(":",A4,1)+1,SEARCH("to",A4,1)-3-SEARCH(":",A4,1)+1)

 

[larinda4]

maybe

Book1
	A	B	C	D
5	For the Period: May 01, 2021 to January 30, 2026			May 01, 2021
Sheet1

Cell Formulas
Range	Formula
D5	D5	=MID(A4,SEARCH(":",A4,1)+1,SEARCH("to",A4,1)-3-SEARCH(":",A4,1)+1)

Hi Kerry!

Thank you so much! It worked. I only made a slight change to your formula because it was bringing in a space at the beginning of the date.

=MID(A5,SEARCH(":",A5,1)+2,SEARCH("to",A5,1)-3-SEARCH(":",A5,1)+1)

You're the best! Have a great day!

 

[Kerryx]

Glad to help

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[larinda4]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hey Fluff,

I took your suggestion and updated my account details to indicate which excel version I am using. Thank you!

 

[JvdV]

Just an alternative:

Question.xlsx
	A	B
1	For the Period: May 01, 2021 to January 30, 2022	May 01, 2021
2		May 01, 2021
Sheet9

Cell Formulas
Range	Formula
B1	B1	=TEXTBEFORE(TEXTAFTER(A1,": ")," to ")
B2	B2	=@DROP(TEXTSPLIT(A1,{": ";" to "}),,1)

If you'd remove the implicit intersection operator you'd end up with both the start- & end-date in seperate columns.

 

[AlanY]

try

=TRIM(MID(A5,SEARCH(":",A5)+1,SEARCH("to",A5)-SEARCH(":",A5)-1))

 

[Fluff]

I took your suggestion and updated my account details

Thanks for that.
As you have 365 another option is

Fluff.xlsm
	A	B
1
2	For the Period: May 01, 2021 to January 30, 2022	May 01, 2021
Main

Cell Formulas
Range	Formula
B2	B2	=INDEX(TEXTSPLIT(A2,{": "," to"}),,2)