Date Formula From Text in Alternate Cell

[ladylissa21]

Hello smart people!

I'm using a formula currently to extract the date out of a text comment, and it's been working well until we hit the double digit months. It enters the single digit months perfectly, as (for example) 09/13/2018. But in the double digit months, it's giving me (for example, in October) 0/23/2018. It's not recognizing that first digit.

So currently L2 has a text comment that reads something like: SERVICING NOTE REVIEW COMPLETED ON 10/24/2018.

And the existing formula in M2 is: =MID(L2,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},L2,1),LEN(L2)+1)),LOOKUP(1,0/MID(L2,ROW(INDIRECT("1:"&LEN(L2))),1),ROW(INDIRECT("1:"&LEN(L2)))) + 1 - MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},L2,1),LEN(L2)+1)))

I found that formula online and have no idea how to alter it to fix the issue. I want it, ideally, to populate the date as given in the note, and ignore any empty fields in L2. Thank you!

 

[jtakw]

You're welcome.

 

[ladylissa21]

Okay, new problem. Some users are putting a period at the end of the sentence. For example, the text in the cell might say, "Servicing Note Review 08/22/2018." The period at the end seems to be stumping the formula. I can do a find/replace if I need to, but first I want to see if there's any adjustment I can make to the formula to have it look past that automatically?

 

[jtakw]

Formula adjusted for your additional requirement:

Book1
	A	B
1	LATE CHARGE NOTE REVIEW COMPLETED ON 08/28/2018___________________	8/28/2018
2	LATE CHARGE NOTE REVIEW COMPLETED ON 09/17/2018___________________	9/17/2018
3	LATE CHARGE NOTE REVIEW COMPLETED ON 10/16/2018___________________	10/16/2018
4	_LATE CHARGE NOTE REVIEW COMPLETED ON 09/12/2018	9/12/2018
5	_LATE CHARGE NOTE REVIEW COMPLETED ON 10/29/2018	10/29/2018
6	SERVICING NOTE REVIEW COMPLETED ON 10/24/2018	10/24/2018
7	SERVICING NOTE REVIEW HAS NOT BEEN COMPLETED
8	SERVICING NOTE REVIEW COMPLETED ON 09/13/2018	9/13/2018
9	SERVCING NOTE REVIEW 5/25/18	5/25/2018
10	SERVICING NOTE REVIEW 11/7/2018	11/7/2018
11	SERVICING NOTE REVIEW 1/1/19	1/1/2019
12	SERVICING NOTE REVIEW ON 09/13/2018 COMPLETED	9/13/2018
13	LATE CHARGE NOTE REVIEW COMPLETED ON 10/16/2018.___________________	10/16/2018
14	SERVICING NOTE REVIEW 11/7/2018.	11/7/2018
Sheet350

Cell Formulas
Range	Formula
B1	=IFERROR(SUBSTITUTE(SUBSTITUTE(LEFT(SUBSTITUTE(MID(A1,MIN(FIND({0,1,2,3,4}+{0;5},A1&1/17)),99)," ",REPT(" ",99)),99),"_",""),".","")+0,"")

 

[ladylissa21]

You've come to my rescue once again, thank you! Here's hoping that's the last variation that I need to account for. I appreciate all of your help so much!

 

[jtakw]

You're welcome, feel free to post back if things change.