Extract dd and mm from text and put that plus current yy in another

[robzrob]

I've got a workbook which comes from someone else and it's got a cell
in it from which I want to get the above. The format of the cell
varies. It could be texttexttext 17Feb09, texttexttext 17th Feb,
texttexttext 17 February 09, texttexttext 17th February 2009, etc (but
never Feb 17 - I'm in UK not US!)

 

[Fred Smith]

Try Text to Columns (in the Data menu). It should convert most every date
for you.

Regards,
Fred

 

[Ron Rosenfeld]

I've got a workbook which comes from someone else and it's got a cell
in it from which I want to get the above. The format of the cell
varies. It could be texttexttext 17Feb09, texttexttext 17th Feb,
texttexttext 17 February 09, texttexttext 17th February 2009, etc (but
never Feb 17 - I'm in UK not US!)

One way would be with a User Defined Function. The function below uses Regular
Expressions to extract that which looks like a date. The expression looks for

a number between 1-31 (with an optional leading 0 for 1-9)
followed by 0 to 3 non-digit characters
followed by the first three letters of a month

It then uses CDate to convert the day month into an Excel date. CDate seems to
default to the current year if year is not specified.


To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and paste the code below into the
window that opens.

To use this User Defined Function (UDF), enter a formula like

=ExtrDate(A1)

in some cell.

==========================================
Option Explicit
Function ExtrDate(s As String) As Date
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Pattern = _
"\b(3[01]|[12]\d|0?[1-9])\D{0,3}(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)"
If re.test(s) = True Then
Set mc = re.Execute(s)
ExtrDate = CDate(mc(0).submatches(0) & " " & mc(0).submatches(1))
End If
End Function
============================================

--ron

 

[robzrob]

I've got a workbook which comes from someone else and it's got a cell
in it from which I want to get the above.  The format of the cell
varies.  It could be texttexttext 17Feb09, texttexttext 17th Feb,
texttexttext 17 February 09, texttexttext 17th February 2009, etc (but
never Feb 17 - I'm in UK not US!)

One way would be with a User Defined Function.  The function below usesRegular
Expressions to extract that which looks like a date.  The expression looks for

        a number between 1-31 (with an optional leading 0 for 1-9)
        followed by 0 to 3 non-digit characters
        followed by the first three letters of a month

It then uses CDate to convert the day month into an Excel date.  CDate seems to
default to the current year if year is not specified.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and paste the code below into the
window that opens.

To use this User Defined Function (UDF), enter a formula like

        =ExtrDate(A1)

in some cell.

==========================================
Option Explicit
Function ExtrDate(s As String) As Date
 Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Pattern = _
"\b(3[01]|[12]\d|0?[1-9])\D{0,3}(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|no­v|dec)"
If re.test(s) = True Then
    Set mc = re.Execute(s)
    ExtrDate = CDate(mc(0).submatches(0) & " " & mc(0).submatches(1))
End If
End Function
============================================

--ron

Thanks. I've tried it but it returns 0 whatever I put in A1.

 

[Ron Rosenfeld]

I've got a workbook which comes from someone else and it's got a cell
in it from which I want to get the above.  The format of the cell
varies.  It could be texttexttext 17Feb09, texttexttext 17th Feb,
texttexttext 17 February 09, texttexttext 17th February 2009, etc (but
never Feb 17 - I'm in UK not US!)

One way would be with a User Defined Function.  The function below uses Regular
Expressions to extract that which looks like a date.  The expression looks for

        a number between 1-31 (with an optional leading 0 for 1-9)
        followed by 0 to 3 non-digit characters
        followed by the first three letters of a month

It then uses CDate to convert the day month into an Excel date.  CDate seems to
default to the current year if year is not specified.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and paste the code below into the
window that opens.

To use this User Defined Function (UDF), enter a formula like

        =ExtrDate(A1)

in some cell.

==========================================
Option Explicit
Function ExtrDate(s As String) As Date
 Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Pattern = _
"\b(3[01]|[12]\d|0?[1-9])\D{0,3}(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|no­v|dec)"
If re.test(s) = True Then
    Set mc = re.Execute(s)
    ExtrDate = CDate(mc(0).submatches(0) & " " & mc(0).submatches(1))
End If
End Function
============================================

--ron

Thanks. I've tried it but it returns 0 whatever I put in A1.

I am seeing one typo in the above:

in the re.pattern line, ...|no-v|... should be ...|nov|...

I used your test strings that you posted, and had no problem returning the
dates for each line.

What, exactly, do you have in A1?

Also be sure that when you copy/pasted in the UDF, that unwanted line wraps did
not cause a problem. In particular, the line after re.Pattern _, that starts
with "\b and ends with |dec)" should all be on a single line.

--ron

 

[robzrob]

I've got a workbook which comes from someone else and it's got a cell
in it from which I want to get the above.  The format of the cell
varies.  It could be texttexttext 17Feb09, texttexttext 17th Feb,
texttexttext 17 February 09, texttexttext 17th February 2009, etc (but
never Feb 17 - I'm in UK not US!)
One way would be with a User Defined Function.  The function below uses Regular
Expressions to extract that which looks like a date.  The expressionlooks for
        a number between 1-31 (with an optional leading 0 for 1-9)
        followed by 0 to 3 non-digit characters
        followed by the first three letters of a month
It then uses CDate to convert the day month into an Excel date.  CDate seems to
default to the current year if year is not specified.
To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and paste the code belowinto the
window that opens.
To use this User Defined Function (UDF), enter a formula like
        =ExtrDate(A1)
in some cell.
==========================================
Option Explicit
Function ExtrDate(s As String) As Date
 Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Pattern = _
"\b(3[01]|[12]\d|0?[1-9])\D{0,3}(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|no­­v|dec)"
If re.test(s) = True Then
    Set mc = re.Execute(s)
    ExtrDate = CDate(mc(0).submatches(0) & " " & mc(0).submatches(1))
End If
End Function
============================================
--ron

Thanks.  I've tried it but it returns 0 whatever I put in A1.

I am seeing one typo in the above:

in the re.pattern line, ...|no-v|...  should be   ...|nov|...

I used your test strings that you posted, and had no problem returning the
dates for each line.

What, exactly, do you have in A1?

Also be sure that when you copy/pasted in the UDF, that unwanted line wraps did
not cause a problem.  In particular, the line after  re.Pattern _, that starts
with "\b  and ends with |dec)" should all be on a single line.

--ron- Hide quoted text -

- Show quoted text -

Hello again. Sorry, I was testing it wrongly. I was just putting
dates in A1 instead of text followed by dates. Have now tried a few
random texts and dates in various formats - it works fine. Thanks
very much.

 

[Ron Rosenfeld]

I've got a workbook which comes from someone else and it's got a cell
in it from which I want to get the above.  The format of the cell
varies.  It could be texttexttext 17Feb09, texttexttext 17th Feb,
texttexttext 17 February 09, texttexttext 17th February 2009, etc (but
never Feb 17 - I'm in UK not US!)

One way would be with a User Defined Function.  The function below uses Regular
Expressions to extract that which looks like a date.  The expression looks for

        a number between 1-31 (with an optional leading 0 for 1-9)
        followed by 0 to 3 non-digit characters
        followed by the first three letters of a month

It then uses CDate to convert the day month into an Excel date.  CDate seems to
default to the current year if year is not specified.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and paste the code below into the
window that opens.

To use this User Defined Function (UDF), enter a formula like

        =ExtrDate(A1)

in some cell.

==========================================
Option Explicit
Function ExtrDate(s As String) As Date
 Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = True
re.Pattern = _
"\b(3[01]|[12]\d|0?[1-9])\D{0,3}(jan|feb|mar|apr|may|jun|jul|aug|sep|oct|no­­v|dec)"
If re.test(s) = True Then
    Set mc = re.Execute(s)
    ExtrDate = CDate(mc(0).submatches(0) & " " & mc(0).submatches(1))
End If
End Function
============================================

--ron

Thanks.  I've tried it but it returns 0 whatever I put in A1.

I am seeing one typo in the above:

in the re.pattern line, ...|no-v|...  should be   ...|nov|...

I used your test strings that you posted, and had no problem returning the
dates for each line.

What, exactly, do you have in A1?

Also be sure that when you copy/pasted in the UDF, that unwanted line wraps did
not cause a problem.  In particular, the line after  re.Pattern _, that starts
with "\b  and ends with |dec)" should all be on a single line.

--ron- Hide quoted text -

- Show quoted text -

Hello again. Sorry, I was testing it wrongly. I was just putting
dates in A1 instead of text followed by dates. Have now tried a few
random texts and dates in various formats - it works fine. Thanks
very much.

Yes, if it is a "real" Excel date, then it is stored as a number and passed to
the function as such. But you should be able, for example, to enter '17-Feb
(as a text string), and have it returned by the UDF.

The UDF depends on being passed a text value, and not a number such as 40011,
which is what happens if A1 contains a real date.

Thanks for the feedback. Glad to help.
--ron