How do I extract multiple date values from variable text string?

[WanttobeExcelNinja]

How do I extract the date values separately from the variable text strings below? I learned how to extract the numbers only from these cells with the following array Ron (Mr. Excel) posted on youtube:

=SUM(MID(0&M14,LARGE(ISNUMBER(--MID(M14,ROW(INDIRECT("1:"&LEN(M14))),1))*ROW(INDIRECT("1:"&LEN(M14))),ROW(INDIRECT("1:"&LEN(M14))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(M14)))-1))

I was trying to find a way to create these date values from the numbers only string the above formula created but could not figure out.

I also tried different formulas to just pull the first date in one cell and pull the second date in a second cell but was running into problems with the variable nature users input this information. I could spend time slicing the data with text to columns and reformatting but I was hoping to discover a formula that would workaround most entry differences. The goal would be to rerun scripts each month and only add new data and have little work reformatting for entry differences.

Any help, suggestions, guidance is much appreciated.

Linedescription	Mr. Excel Numbers only formula
Water Escrows 1/1/2015-1/31/15	11201513115
BROCHI- 2014 Water Escrow Catch-up 1/1/14-8/31/14	2014111483114
Water Usage 3/31/14 - 6/30/14	3311463014
Water Usage 09/12/14 - 10/14/14	91214101414
Water Usage -4/28/14-5/28/14	4281452814
Water Usage 4/30/14-5/30/14	4301453014
Water True-up 4/30/14 - 6/30/14	4301463014
Water True-up 6/30/14-9/2/14	630149214
Water Usage (07/11/14-08/06/14)	71114080614
Water Usage 5/23/14-6/25/14	5231462514
Water Usage 09/11/14-10/10/14	91114101014

<tbody>
</tbody>

Kind Regards,

WanttobeexcelNinja

 

[Blade Hunter]

Messy because the data is so inconsistent but it works:

First Date:
=SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," - "," ")," "," ")," "," "),"-"," ")," ",REPT(" ",255)),510),255)),"(",""),")","")

Second Date:
=SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," - "," ")," "," ")," "," "),"-"," ")," ",REPT(" ",255)),255)),"(",""),")","")

 

[VBA Geek]

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=ExtractDate(<font color="Blue">$A1,COLUMNS(<font color="Red">$B1:B1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />


UDF to ADD:

Function ExtractDate(S As String, MatchNumber As Integer) As String
Static RegExp As Object


If RegExp Is Nothing Then
    Set RegExp = CreateObject("vbscript.regexp")
    RegExp.Pattern = "(\d?\d/){2}\d{2,4}"
    RegExp.Global = True
End If


ExtractDate = RegExp.Execute(S).Item(MatchNumber - 1).Value
End Function

RESULTS:

Excel 2013
	A	B	C
1	Water Escrows 1/1/2015-1/31/15	1/1/2015	1/31/15
2	BROCHI- 2014 Water Escrow Catch-up 1/1/14-8/31/14	1/1/14	8/31/14
3	Water Usage 3/31/14 - 6/30/14	3/31/14	6/30/14
4	Water Usage 09/12/14 - 10/14/14	09/12/14	10/14/14
5	Water Usage -4/28/14-5/28/14	4/28/14	5/28/14
6	Water Usage 4/30/14-5/30/14	4/30/14	5/30/14
7	Water True-up 4/30/14 - 6/30/14	4/30/14	6/30/14
8	Water True-up 6/30/14-9/2/14	6/30/14	9/2/14
9	Water Usage (07/11/14-08/06/14)	07/11/14	08/06/14
10	Water Usage 5/23/14-6/25/14	5/23/14	6/25/14
11	Water Usage 09/11/14-10/10/14	09/11/14	10/10/14
Sheet1

 

[Jonmo1]

Here's how I did it..

First Date
=TRIM(MID(TRIM(SUBSTITUTE(A2,"-"," ")),SEARCH("/",TRIM(SUBSTITUTE(A2,"-"," ")))-2,FIND(" ",TRIM(SUBSTITUTE(A2,"-"," ")),SEARCH("/",TRIM(SUBSTITUTE(A2,"-"," "))))-SEARCH("/",TRIM(SUBSTITUTE(A2,"-"," ")))+2))+0

2nd Date
=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2,")",""),"-",REPT(" ",LEN(A2))),LEN(A2)))+0

 

[Blade Hunter]

Very nice VBA Geek

 

[WanttobeExcelNinja]

Thank you Blade Hunter, Jonmo1, and VBA Geek for your feedback. It is much appreciated!

 

[WanttobeExcelNinja]

Hello VBA Geek - Is there something I need to change in the formula or vba code to get the correct end date values in column c? I used what you provided for column B and it worked perfectly.

 

[VBA Geek]

normally you just need to enter the formula in b1 ans drag across and down

 

[WanttobeExcelNinja]

normally you just need to enter the formula in b1 ans drag across and down

Worked perfectly!

 

[István Hirsch]

Try the formula in:

B2:

=TRIM(SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("/",A2)-2,FIND("-",A2,FIND("/",A2))-FIND("/",A2)+2),"(",""),"-",""))

C2:

=TRIM(SUBSTITUTE(SUBSTITUTE(MID(A2,FIND("-",A2,FIND("/",A2)),20),")",""),"-",""))

Excel Workbook
	A	B	C
1
2	Water Escrows 1/1/2015-1/31/15	1/1/2015	1/31/15
3	BROCHI- 2014 Water Escrow Catch-up 1/1/14-8/31/14	1/1/14	8/31/14
4	Water Usage 3/31/14 - 6/30/14	3/31/14	6/30/14
5	Water Usage 09/12/14 - 10/14/14	09/12/14	10/14/14
6	Water Usage -4/28/14-5/28/14	4/28/14	5/28/14
7	Water Usage 4/30/14-5/30/14	4/30/14	5/30/14
8	Water True-up 4/30/14 - 6/30/14	4/30/14	6/30/14
9	Water True-up 6/30/14-9/2/14	6/30/14	9/2/14
10	Water Usage (07/11/14-08/06/14)	07/11/14	08/06/14
11	Water Usage 5/23/14-6/25/14	5/23/14	6/25/14
12	Water Usage 09/11/14-10/10/14	09/11/14	10/10/14
13	Water Usage (7/11/14-08/06/14)	7/11/14	08/06/14
Sheet