Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting data from rows where one field is blank | Excel Discussion (Misc queries) | |||
Find and Replace in the Comment Field | Excel Discussion (Misc queries) | |||
Extracting month from a date field | Excel Discussion (Misc queries) | |||
How to Join/concatenate a date field with a time field in Excel? | Excel Discussion (Misc queries) |