PDA

View Full Version : [SOLVED] Extracting Date from a string of Text - VBA Coding Help



DavidLee91
06-24-2015, 09:10 PM
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.

: pray2:

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
06-25-2015, 12:34 AM
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
06-25-2015, 01:08 AM
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. :bow:

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
06-25-2015, 01:17 AM
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
06-25-2015, 03:59 AM
Hi Excelliot,

THANKS you so so so much for your help!

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

Regards,
David

excelliot
06-25-2015, 04:32 AM
Great, Do mark thread as complete & click on * to add reputation to mine reply.

Cheers!!
www.excelliot.com