Extract Dates from String

**djblois1** · 07-15-2011, 11:31 AM

I want to be able to extract dates (where excel recognizes it as a date) from a string.

Here are some samples:

From JAN-01-2011 To JAN-15-2011
From JAN-01-2010

it will always be in one of those two formats but the dates will change.

**davegugg** · 07-15-2011, 11:46 AM

Sub extract()

    Dim stra(2) As String
    Dim dte As Date
    
    stra(0) = "FROM JAN-01-2011 To JAN-15-2011"
    stra(1) = "FROM JAN-01-2010"
    
    For i = 0 To 1
        If Len(stra(i)) > 16 Then
            dte = CDate(Right(stra(i), 11))
            MsgBox dte
            dte = CDate(Right(Left(stra(0), 16), 11))
            MsgBox dte
        Else
            dte = CDate(Right(stra(i), 11))
            MsgBox dte
        End If
    Next i

End Sub

Here is one way to find the three dates.

**snb** · 07-15-2011, 12:14 PM

sub snb()
  sq = Filter(Split("From JAN-01-2011 To JAN-15-2011"), "-")
  for j=0 to ubound(sq)
    x1 = CDate(sq(j))
  next
End Sub

**djblois1** · 07-15-2011, 01:17 PM

Originally Posted by snb

sub snb()
  sq = Filter(Split("From JAN-01-2011 To JAN-15-2011"), "-")
  for j=0 to ubound(sq)
    x1 = CDate(sq(j))
  next
End Sub

snb,

That works great!! You are the man. I just need to do three other things with it:

1) They need to be saved in two separate variables
2) They need to be in date format
3) It also needs to work if the sting only has one date in it (like the example I gave) - I think this one, I can just use part of davegugg's code but if you can think of a more efficient way, then I am all ears.

btw, davegugg thank you for your help too. I appreciate everyone's help. And you gave me a few ideas as well.

**djblois1** · 07-15-2011, 02:09 PM

Here is what I have so far:

Sub snb()

Dim j As Integer
Dim sq As Variant
Dim stra As Variant

    sq = Filter(Split("From JAN-01-2011 To JAN-15-2011"), "-")
 
    If Len(stra(i)) > 16 Then
        For j = 0 To UBound(sq)
            x1 = CDate(sq(j))
        Next
    Else
    
    End If
    
End Sub

I need to do 3 things with this code now:

1) They need to be saved in two separate variables
2) They need to be in date format
3) It also needs to work if the sting only has one date in it (like the example I gave)

**davegugg** · 07-15-2011, 02:12 PM

The code I posted was already set up to do those three things:

Sub extract()

    Dim stra(2) As String
    Dim dte1 As Date
    Dim dte2 As Date
    
    stra(0) = "FROM JAN-01-2011 To JAN-15-2011"
    stra(1) = "FROM JAN-01-2010"
    
    For i = 0 To 1
        If Len(stra(i)) > 16 Then
            dte1 = CDate(Right(stra(i), 11))
            dte2 = CDate(Right(Left(stra(0), 16), 11))
        Else
            dte1 = CDate(Right(stra(i), 11))
        End If
    Next i

End Sub

**djblois1** · 07-15-2011, 02:21 PM

Thank you - it actually works great. Quick question:

does this:

Dim stra(2) As String

mean that I can use a stra(0), Stra(1), and a Stra(2)?

if so why didn't you use:

Dim Dte(2) as Date

Can I use that for any variable?

**davegugg** · 07-15-2011, 02:31 PM

It declares the variable as an array with three members. I really only needed two. You can easily find more info on vba arrays by doing a quick Google search. You could also set up the date variable as an array.