View Full Version : [SOLVED:] Extract dates from a cell containing a string of data
vmehra
03-24-2021, 06:06 AM
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
03-24-2021, 07:11 AM
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
03-24-2021, 07:33 AM
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
03-24-2021, 08:09 AM
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
03-24-2021, 08:51 AM
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
03-24-2021, 09:11 AM
Sorry I don't know how to use this in excel
The data has "to" 99% of time
vmehra
03-24-2021, 09:11 AM
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
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
03-24-2021, 10:20 AM
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
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
03-24-2021, 11:39 AM
Perfect - thank you SamT and all others for their contribution. A quick resolution that has saved me lots of time
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.