Extract dates from a cell containing a string of data

[vmehra]

I have a set of date with information in column similar to the below


Commission 13% Quarterly for the period of 25/01/2021 to 24/04/2021 (Part paid)



The text in the cell varies but it always contact a "from" date and a "to" date
In this case "from" being 25/01/2021 and "to" being 24/04/2021

I want to extract the "from" and "to" dates into columns alongside the original data

Most of the date formats are in this format dd/mm/yyyy
But to make it complicated sometimes it is in the format dd/mm/yy (or very rarely in other formats)
But this is very occasional and if I can solve the majority of the data then I will be very happy

[KOKOSEK]

Something like this:


A
B
C

1Commission 13% Quarterly for the period of 25/01/2021 to 24/04/2021 (Part paid)
25/01/2021
24/04/2021






A


B


C




1

Commission 13% Quarterly for the period of 25/01/2021 to 24/04/2021 (Part paid)

=MID($A$1,FIND("of",$A$1)+3,10)*1


=MID($A$1,FIND("to",$A$1)+3,10)*1

[vmehra]

Something like this:





A


B


C




1

Commission 13% Quarterly for the period of 25/01/2021 to 24/04/2021 (Part paid)

25/01/2021


24/04/2021









A


B


C




1

Commission 13% Quarterly for the period of 25/01/2021 to 24/04/2021 (Part paid)

=MID($A$1,FIND("of",$A$1)+3,10)*1


=MID($A$1,FIND("to",$A$1)+3,10)*1





Thank you for the suggestion
The problem is the text varies cell to cell (but always contains the from / to dates)

So this doesn't quite work
The next cell down for example is


Commission 14% Quarterly - 30/01/2021 to 29/04/2021

[KOKOSEK]

I used you first example. Is a word TO always exists? or also can be missing.
Could you give more significant examples of possibilities.

[JKwan]

give this a go

Sub Parse()
Sub Parse()
Dim sText As String
Dim sFrom As String
Dim sTo As String
Dim sParts As Variant

sText = Cells(1, "A")
sParts = Split(sText, "/")
sFrom = Right(sParts(0), 2) & "/"
sFrom = sFrom & sParts(1) & "/"
sFrom = sFrom & Left(sParts(2), 4)
sTo = Right(sParts(2), 2) & "/"
sTo = sTo & sParts(3) & "/"
sTo = sTo & Left(sParts(4), 4)

MsgBox sFrom & " to " & sTo
End Sub

[vmehra]

Sorry I don't know how to use this in excel
The data has "to" 99% of time

[vmehra]

Here is some more data if it helps
(ps really appreciate the replies)



Commission 16% Quarterly for the period of 24/01/2021 to 23/04/2021


Commission 14% Quarterly for the period of 31/01/2021 to 29/04/2021


c/n Commission 14% Quarterly for the period of 20/01/2021 to 19/04/2021


Commission 14% Quarterly for the period of 20/01/2021 to 19/04/2021 (Part paid)


Commission 14% Quarterly - 30/01/2021 to 29/04/2021


Commission 14% for 20/01/2021 to 26/03/2021


Commission 14% Quarterly - 12/12/2020 to 11/03/2021


Commission 14% Quarterly for the period of 09/01/2021 to 08/04/2021


Commission 14% Quarterly for the period of 30/01/2021 to 29/04/2021


Commission 14% for the Period of 01/02/2021 to 31/03/2021 (Part paid)


Commission 14% Quarterly for the Period of 02/02/2021 to 01/05/2021


c/n Commission 14% 6-Mthly for the period of 12/01/2021 to 11/07/2021


Commission 14% 6-Mthly for the Period of 01/02/2021 to 31/07/2021


Commission 14% 6-Mthly for the period of 30/01/2021 to 29/07/2021


c/n Commission 14% 6-Mthly for the period of 30/01/2021 to 29/07/2021


Commission 14% 6-Mthly for the period of 30/01/2021 to 29/07/2021


c/n Commission 14% - 31/12/2020 to 23/02/2021

[SamT]

Function FromDate(Cel As Range) As Date
Dim Tmp, i
Tmp = Split(Cel.Value, " ")
For i = Lbound(tmp) to UBound(tmp)
If IsDate(Tmp(i) Then
FromDate = Format(Tmp(i), "dd/mm/yyyy")
Exit Function
End If
End Function



Function ToDate(Cel As Range) As Date
Dim Tmp, i
Tmp = Split(Cel.Value, " ")
For i = Ubound(tmp) to LBound(tmp)
If IsDate(Tmp(i) Then
ToDate = Format(Tmp(i), "dd/mm/yyyy")
Exit Function
End If
End Function

Example Formulas: ColumnB: "=FromDate($A1)",,, Column C: "=ToDate($A1)"

Open VBA (Right Click a Tab, Select "View Code") >> Insure Project Explorer is open (Ctrl+R) >> Right Click on "ThisWorkbook" >> Select "Insert" >> Select "Module"

Paste the Two Functions above into that Module

Use the Example Formulas in the appropriate columns. Copy Down.

If you don't get a date or the date is 01/01/1900, the Dates in the string are not present or not recognizable as Dates

[vmehra]

Maybe I am doing someting wrong but I am getting a Complie error:Syntax Error with this line highlighted (for both functions)
If IsDate(Tmp(i) Then

The date format looks ok


Commission 14% Quarterly - 30/01/2021 to 29/04/2021

[SamT]

My bad. I forgot a ")". I also tested and edited both Procedures.


Function FromDate(Cel As Range) As Date
Dim Tmp, i
Tmp = Split(Cel.Value, " ")
For i = LBound(Tmp) To UBound(Tmp)
If IsDate(Tmp(i)) Then
FromDate = Tmp(i)
Exit Function
End If
Next i
End Function


Function ToDate(Cel As Range) As Date
Dim Tmp, i
Tmp = Split(Cel.Value, " ")
For i = UBound(Tmp) To LBound(Tmp) Step -1
If IsDate(Tmp(i)) Then
ToDate = Tmp(i)
Exit Function
End If
Next i
End Function

[vmehra]

Perfect - thank you SamT and all others for their contribution. A quick resolution that has saved me lots of time