Can you extract dates from text

[Abhijeet]

Mod Edit:
This thread is a cross-post from a previous thread started by author in this forum.
http://chandoo.org/forum/threads/give-dates-in-format.25311
Please continue discussion there.
=======================

Hi

My date of birth is 07/01/2015
Hmm, 07/01 I think that is when I was born
Oh wait, was it 15/05 I think so
07/01/1981 that is when I landed here.

Let me ask my wife, No wait, I think it is 07/01/2015

I want pull only dates in next columns please tell me how to pull this

 

[Marc L]

Hi !

Easy using Split and a loop for each splited data with IsDate function.
CDate function may help also …
Another way is to just check if exists "/" character in splited data with InStr.​

 

[Abhijeet]

Hi Marc L

If splited data is not date then i want to delete.I want only Date please tell me how to do this

 

[Marc L]

Yet explained in my previous post …

​

 

[Abhijeet]

Yet explained in my previous post …

​

Yes u told loop but i am unable to do this can u pls tell me how to do this

 

[Luke M]

What have you tried so far? What code have you written? You know how to write loops. You know how to use the Split function. You know how to use the IsDate function.

 

[Abhijeet]

Sub Split1()
For Each Rng In ActiveSheet.Range("a1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
arr = Split(Rng, " ")
Rng.Offset(0, 1).Resize(1, UBound(arr) + 1).Value = arr
Next
End Sub

End Sub

What have you tried so far? What code have you written? You know how to write loops. You know how to use the Split function. You know how to use the IsDate function.

I tried this code but i do not know if that splited is not date then deleted those data pls tell me

 

[Luke M]

You didn't use [code] and [/code] tags in your post. Please edit and correct.

 

[Marc L]

Sub Demo()
With Cells(1).CurrentRegion.Columns(1)
                         VA = .Value
    For R& = 1 To UBound(VA)
        For Each V In Split(VA(R, 1))
            If InStr(V, "/") Then VA(R, 1) = V: Exit For
        Next
                   If V = "" Then VA(R, 1) = ""
    Next
    With .Offset(, 1):  .NumberFormat = "@":  .Value = VA:  End With
End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Abhijeet]

for this line For R& = 1 To UBound(VA) Debug error help on this

 

[Luke M]

Better. Once you have the split, loop over each item in the split and check if it's a date

Sub Split1()
Dim i As Long
Dim arr As Variant
Dim Rng As Range
Dim recCount As Long

Application.ScreenUpdating = False
'Where are we looking
For Each Rng In ActiveSheet.Range("a1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    arr = Split(Rng, " ")
    recCount = 1
    'Check each part of the array we split
    For i = LBound(arr) To UBound(arr)
        'Verify it's a date
        If IsDate(arr(i)) Then
            'Export to sheet
            Rng.Offset(0, recCount).Value = arr(i)
            recCount = recCount + 1
        End If
    Next i
Next Rng
Application.ScreenUpdating = True
End Sub

 

[Marc L]

for this line For R& = 1 To UBound(VA) Debug error help on this

Works like a breeze on my side ‼

​

 

[Abhijeet]

Thanks Luke M & Marc L

Luke M ur code is work perfect Marc L i do not know on my system its not work

 

[Marc L]

So it's your data …

According to rollin' banner second advice before log in,
it's the last time I answer you without an attachment !
​

 

[Marc L]

No needs a loop using Match Excel worksheet function …

​

 

[Abhijeet]

Hi Marc L

I was not uploaded data sorry for that

Here i am upload the file pls tell me now how to do this i want pull the dates from this data

 

[Marc L]

Not the same data as initial post and you don't precise
what is your expected result !

It looks like your previous thread where you decided to not answer
and where I have already some working ways …

If Luke or anyone else wants to continue here …
Otherwise, for that kind of data, I stay on your previous thread,
not on a duplicate one !
​

 

[Abhijeet]

Not the same data as initial post and you don't precise
what is your expected result !

It looks like your previous thread where you decided to not answer
and where I have already some working ways …

If Luke or anyone else wants to continue here …
Otherwise, for that kind of data, I stay on your previous thread,
not on a duplicate one !
​

Sorry Marc L
I know my previous post not like this data now i post expected result data in attach file please help me

 

[Marc L]

So if it's not the same kind of data you should be easily able
to amend Luke's code … I definitively quit this thread.

​

 

[Luke M]

Locking this thread as it's been disguised as a cross-post from previous thread.