extracting a date from a comment field in an excel file

[dave]

I have exported data from a database into Excel. One of the fields is a
comment field that includes a date in the format of m/dd or m/d. It
typically follows the phrase LSD or LSD=. For example the comment may read,
"Awaiting LSD 12/27" or "Sale is pending LSD=1/9". I want to be able to
extract the date only from this comment field and have it populate in a new
column. Is it possible to do this and how do I go about it? Thanks.

 

[Ron Rosenfeld]

I have exported data from a database into Excel. One of the fields is a
comment field that includes a date in the format of m/dd or m/d. It
typically follows the phrase LSD or LSD=. For example the comment may read,
"Awaiting LSD 12/27" or "Sale is pending LSD=1/9". I want to be able to
extract the date only from this comment field and have it populate in a new
column. Is it possible to do this and how do I go about it? Thanks.

Here is a UDF that will return the date from either of the above formats --
actually it will return the m/dd m/d or mm/d formats so long as it is separated
from the rest of the string with a character that is not a letter, digit or
underscore.

To enter this, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

Then use the function =GetDt(cell_ref) in some cell to return the date.

The function returns the date as a string. If you return it as a date, then
Excel will append the current year to the date.

Let me know if this does what you want.

==============================================
Option Explicit
Function GetDt(str As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\b(0?[1-9]|1[012])/(0?[1-9]|[12][0-9]|3[01])\b"

Set mc = re.Execute(str)
If mc.Count >= 1 Then
GetDt = mc(0).Value
Else
GetDt = ""
End If
End Function
===================================================
--ron

 

[Ron Rosenfeld]

By the way, if the information is truly in a comment field (i.e. attached to
the cell as a comment), as opposed to being in a cell that you are calling a
comment field, then try this instead:

======================================
Function GetDtFromComment(cell_ref As Range) As String
Dim re As Object, mc As Object
Dim str As String
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\b(0?[1-9]|1[012])/(0?[1-9]|[12][0-9]|3[01])\b"

On Error Resume Next
str = cell_ref.Comment.Text
On Error GoTo 0

Set mc = re.Execute(str)
If mc.Count >= 1 Then
GetDtFromComment = mc(0).Value
Else
GetDtFromComment = ""
End If
End Function
===========================================
--ron

 

[Lars-Åke Aspelin]

I have exported data from a database into Excel. One of the fields is a
comment field that includes a date in the format of m/dd or m/d. It
typically follows the phrase LSD or LSD=. For example the comment may read,
"Awaiting LSD 12/27" or "Sale is pending LSD=1/9". I want to be able to
extract the date only from this comment field and have it populate in a new
column. Is it possible to do this and how do I go about it? Thanks.

Assuming "LSD" occurs once and only once in each comment and that the
date you look for follows with a distance of exactly one character,
the following formula might suit your needs.

If the comment is in cell A1, then put the following in cell B1

=RIGHT(A1,LEN(A1)-SEARCH("LSD",A1)-3)

You can use the TRIM function to remove any possible trailing blanks

Hope this helps. / Lars-Åke