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.


[TABLE="width: 651"]
<tbody>[TR]
[TD]Linedescription[/TD]
[TD]Mr. Excel Numbers only formula[/TD]
[/TR]
[TR]
[TD]Water Escrows 1/1/2015-1/31/15[/TD]
[TD="align: right"]11201513115[/TD]
[/TR]
[TR]
[TD]BROCHI- 2014 Water Escrow Catch-up 1/1/14-8/31/14[/TD]
[TD="align: right"]2014111483114[/TD]
[/TR]
[TR]
[TD]Water Usage 3/31/14 - 6/30/14[/TD]
[TD="align: right"]3311463014[/TD]
[/TR]
[TR]
[TD]Water Usage 09/12/14 - 10/14/14[/TD]
[TD="align: right"]91214101414[/TD]
[/TR]
[TR]
[TD]Water Usage -4/28/14-5/28/14[/TD]
[TD="align: right"]4281452814[/TD]
[/TR]
[TR]
[TD]Water Usage 4/30/14-5/30/14[/TD]
[TD="align: right"]4301453014[/TD]
[/TR]
[TR]
[TD]Water True-up 4/30/14 - 6/30/14[/TD]
[TD="align: right"]4301463014[/TD]
[/TR]
[TR]
[TD]Water True-up 6/30/14-9/2/14[/TD]
[TD="align: right"]630149214[/TD]
[/TR]
[TR]
[TD]Water Usage (07/11/14-08/06/14)[/TD]
[TD="align: right"]71114080614[/TD]
[/TR]
[TR]
[TD]Water Usage 5/23/14-6/25/14[/TD]
[TD="align: right"]5231462514[/TD]
[/TR]
[TR]
[TD]Water Usage 09/11/14-10/10/14[/TD]
[TD="align: right"]91114101014[/TD]
[/TR]
</tbody>[/TABLE]


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