Extract Date from string using Excel VBA

[SANTUPAL]

How to extract date from the string "Due on 01/04/2009-Opening balance adj" using excel vba

 

[wideboydixon]

I'm a little tentative to post this because I feel there's much more complexity coming in follow up but you could just use a formula:

Book1
	A	B
1	Due on 01/04/2009-Opening balance adj	01/04/2009
Sheet1

Cell Formulas
Range	Formula
B1	=DATEVALUE(MID(A1,FIND("/",A1)-2,10))

WBD

 

[SANTUPAL]

Please write the VBA code

	A	B
Due on 01/04/2009-Opening balance adj

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]01/04/2009[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=DATEVALUE(MID(A1,FIND("/",A1)-2,10))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



WBD[/QUOTE]

 

[SANTUPAL]

Write the VBA code to extract date from the string "[TABLE="class: cms_table"]
<tbody>[TR]
[TD]Due on 01/04/2009-Opening balance adj[/TD]
[TD="align: right"]01/04/2009"[/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

Book1
	A	B
1	Due on 01/04/2009-Opening balance adj	01/04/2009
Sheet1

Cell Formulas
Range	Formula
B1	=DateFromString(A1)

Public Function DateFromString(inputString As String) As Date

DateFromString = CDate(Mid(inputString, InStr(1, inputString, "/") - 2, 10))

End Function

WBD

 

[SANTUPAL]

How to know whether a String contains a date value within it

	A	B
Due on 01/04/2009-Opening balance adj

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]01/04/2009[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=DateFromString(A1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Public Function DateFromString(inputString As String) As Date

DateFromString = CDate(Mid(inputString, InStr(1, inputString, "/") - 2, 10))

End Function

WBD[/QUOTE]

 

[wideboydixon]

What would be ideal is if you could state *all* your requirements rather than adding additional comments and suggestions as we go. What do you want to achieve?

WBD

 

[SANTUPAL]

What would be ideal is if you could state *all* your requirements rather than adding additional comments and suggestions as we go. What do you want to achieve?

WBD

I have different strings in different cells.Some of these strings containing date and some are not.I have to check if a string contains any date then is the date value is lesser than a specific date.[Please Write the solution using VBA]

 

[SANTUPAL]

I have different strings( like "Due on 01/04/2009-Opening balance adj", "WRONG","Due on 12/04/2010-Sl no 12 scroll no 123" etc) in different cells.Some of these strings containing date and some are not.I have to check if a string contains any date then is the date value is lesser than a specific date.

 

[wideboydixon]

OK. This function will return either a date or zero (if no date is found):

Public Function GetDateFromString(inputString As String) As Date

Dim regEx As Object
Dim dateParts() As String

Set regEx = CreateObject("VBScript.RegExp")
GetDateFromString = 0

With regEx
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = "\d{1,2}\/\d{1,2}\/\d{4}"
    If .Test(inputString) Then
        dateParts = Split(.Execute(inputString).Item(0).Value, "/")
        GetDateFromString = DateSerial(CLng(dateParts(2)), CLng(dateParts(1)), CLng(dateParts(0)))
    End If
End With

End Function

It uses regular expressions to find a date in the string.

WBD