extracting a date from a comment field in an excel file
 

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.
--
dave

extracting a date from a comment field in an excel file
 

On Wed, 19 Dec 2007 08:28:06 -0800, dave
wrote:

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

extracting a date from a comment field in an excel file
 

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

extracting a date from a comment field in an excel file
 

On Wed, 19 Dec 2007 08:28:06 -0800, dave
wrote:

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