Extracting Date from a string of Text - VBA Coding Help

[DavidLee91]

Hi Guys!

My sheet1 Cell B1 Contains the following text: "Report 20JUN2015".

How Can I extract the date and stores them in variables dteFromDate? I would like to maintain the format of "20JUN2015" while storing this into dteFromDate.

I have the following VBA codes, however, it only work if my text is: "Report 19/6/2015". How can I edit the following code to suit my need?

Any helps would be appreciated guys! PS: The following codes wasnt coded by me. I got this from MREXCEL.com.



THANKS folks!

Option Explicit
    
Sub CallIt()
Dim MyDates() As Date
Dim dteFromDate As Date
    If DateRet(Range("B1").Text, MyDates) Then
        dteFromDate = MyDates(0)
          MsgBox (dteFromDate)
    Else
        '... something to handle if the string is glitched in some manner.
    End If
End Sub
    
Function DateRet(ByVal CellText As String, Dates() As Date) As Boolean
Static REX          As Object ' RegExp
Dim rexMC           As Object ' MatchCollection
Dim aryTemp(0 To 1) As Date
Dim ValsSplit       As Variant
    
    If REX Is Nothing Then
        Set REX = CreateObject("VBScript.RegExp")
        With REX
            .Global = True
            .Pattern = "\b[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{4}\b"
        End With
    End If
    
    With REX
        If .Test(CellText) Then
            Set rexMC = .Execute(CellText)
            '// Since we needed Global = True to return both dates in the string,   //
            If rexMC.Count = 1 Then
                ValsSplit = Split(.Execute(CellText)(0), "/")
                aryTemp(0) = DateSerial(ValsSplit(2), ValsSplit(0), ValsSplit(1))
                Dates() = aryTemp
                DateRet = True
            Else
                DateRet = False
            End If
        Else
            DateRet = False
        End If
    End With
End Function

[excelliot]

Try this simple method:

Sub CallIt()
dtTxt = Replace(Range("B1").Value, "Report ", "")
Range("C1").Value = dtTxt
dtTxt = Range("C1").Value: Range("C1").Value = ""
MsgBox dtTxt
End Sub

Also you can use below formula to get date:

=SUBSTITUTE(B1,"Report ","")*1

you need to format that cell as date.

Cheers!
excelliot.com

[DavidLee91]

Sub CallIt()
dtTxt = Replace(Range("B1").Value, "Report ", "")
Range("C1").Value = dtTxt
dtTxt = Range("C1").Value: Range("C1").Value = ""
MsgBox dtTxt
End Sub

Hi Excelliot

Thank you for your reply!! Your codes is excatly just what I am looking for. Simple and easy to understand.

However, I have 1 last request. How do I modify the codes if my text is : "Report QA 20JUNE2015.list". I need to get rid of the "Report QA" and ".list".

Thanks sooo much for your help!

David

[excelliot]

Try this:

Sub CallIt_2()
dtTxt = Replace(Range("B1").Value, "Report QA ", "")
dtTxt = Replace(dtTxt, ".list", "")
Range("C1").Value = dtTxt
dtTxt = Range("C1").Value: Range("C1").Value = ""
MsgBox dtTxt
End Sub

Cheers!!
excelliot.com

[DavidLee91]

Hi Excelliot,

THANKS you so so so much for your help!

This is what I am exactly looking for! Really appreciate for your help!

Regards,
David

[excelliot]

Great, Do mark thread as complete & click on * to add reputation to mine reply.

Cheers!!
www.excelliot.com