Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
I have the following text string in cell A1. Flight Schedule - Friday 28th
November 2008. How can I extract the date from the text string and paste it in cell A2. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
In A2 enter:
=DATEVALUE(RIGHT(SUBSTITUTE(A1,"th",""),16)) and format as Date -- Gary''s Student - gsnu200816 "Woodi2" wrote: I have the following text string in cell A1. Flight Schedule - Friday 28th November 2008. How can I extract the date from the text string and paste it in cell A2. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
I'd use a VBA function:
Function GetDate(DS As String) As Date Dim N As Long Dim S As String N = InStr(1, DS, " - ", vbBinaryCompare) S = Mid(DS, N + 3) N = InStr(1, S, " ", vbBinaryCompare) S = Mid(S, N + 1) S = Replace( _ Replace( _ Replace( _ Replace(S, "nd", vbNullString), _ "rd", vbNullString), _ "th", vbNullString), _ "st", vbNullString) GetDate = DateValue(S) End Function Then call this from cell A2 as =GetDate(A1) Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 28 Nov 2008 09:35:02 -0800, Woodi2 wrote: Flight Schedule - Friday 28th November 2008. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
=DATEVALUE(RIGHT(SUBSTITUTE(A1,"th",""),16)) I'm assuming that the date in A1 can vary, so hard coding the "th" and the 16 won't work. A1 can have different ordinal suffices and month names different in length than "November". Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 28 Nov 2008 09:59:01 -0800, Gary''s Student wrote: =DATEVALUE(RIGHT(SUBSTITUTE(A1,"th",""),16)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
A little bit more compact function...
Function GetDate(DS As String) As Date Dim Parts() As String Parts = Split(Trim(Mid(DS, InStrRev(DS, "-") + 1)), " ") GetDate = CDate(Val(Parts(1)) & " " & Parts(2) & " " & Val(Parts(3))) End Function -- Rick (MVP - Excel) "Chip Pearson" wrote in message ... I'd use a VBA function: Function GetDate(DS As String) As Date Dim N As Long Dim S As String N = InStr(1, DS, " - ", vbBinaryCompare) S = Mid(DS, N + 3) N = InStr(1, S, " ", vbBinaryCompare) S = Mid(S, N + 1) S = Replace( _ Replace( _ Replace( _ Replace(S, "nd", vbNullString), _ "rd", vbNullString), _ "th", vbNullString), _ "st", vbNullString) GetDate = DateValue(S) End Function Then call this from cell A2 as =GetDate(A1) Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 28 Nov 2008 09:35:02 -0800, Woodi2 wrote: Flight Schedule - Friday 28th November 2008. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
This formula will do what you want (providing there is always a space after
the dash and there is no period after the year)... =--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(MID(A1, FIND(" ",A1,FIND("-",A1)+2),LEN(A1))),"rd",""),"nd",""),"st","") -- Rick (MVP - Excel) "Woodi2" wrote in message ... I have the following text string in cell A1. Flight Schedule - Friday 28th November 2008. How can I extract the date from the text string and paste it in cell A2. Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
Hi,
You can also use the following array formula (Ctrl+Shift+Enter). I have assumed that A14 holds Flight Schedule - Friday 28th November 2008 =MID(A14,MATCH(TRUE,ISNUMBER(1*MID(A14,ROW($1:$100 ),1)),0),50) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Woodi2" wrote in message ... I have the following text string in cell A1. Flight Schedule - Friday 28th November 2008. How can I extract the date from the text string and paste it in cell A2. Thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
"Rick Rothstein" wrote...
This formula will do what you want (providing there is always a space after the dash and there is no period after the year)... =--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(MID(A1, FIND(" ",A1,FIND("-",A1)+2),LEN(A1))),"rd",""),"nd",""),"st","") .... Your formula fails to delete the ordinal suffix "th", which, inconveniently, appears in the OP's example data, so your formula fails for the OP's sample data. Probably best to use regular expressions to do this. For example, using Laurent Longre's MOREFUNC.XLL add-in, you could use the formula =--REGEX.SUBSTITUTE(A1,"^[^-]*-\s*\S+\s+(\d+)[^ 0-9]*\s+(\S+)\s+(\d+).* $","[1]-[2]-[3]") |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
On Fri, 28 Nov 2008 14:27:41 -0600, Chip Pearson wrote:
I'd use a VBA function: Function GetDate(DS As String) As Date Dim N As Long Dim S As String N = InStr(1, DS, " - ", vbBinaryCompare) S = Mid(DS, N + 3) N = InStr(1, S, " ", vbBinaryCompare) S = Mid(S, N + 1) S = Replace( _ Replace( _ Replace( _ Replace(S, "nd", vbNullString), _ "rd", vbNullString), _ "th", vbNullString), _ "st", vbNullString) GetDate = DateValue(S) End Function Then call this from cell A2 as =GetDate(A1) Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 28 Nov 2008 09:35:02 -0800, Woodi2 wrote: Flight Schedule - Friday 28th November 2008. Chip, You also need to replace the "." at the end of the string, if you are going to use this approach. --ron |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
On Fri, 28 Nov 2008 09:35:02 -0800, Woodi2
wrote: I have the following text string in cell A1. Flight Schedule - Friday 28th November 2008. How can I extract the date from the text string and paste it in cell A2. Thanks All of the functional examples assume that there is a hyphen separating the date from the preceding text. Will that always be the case? And will there only be the one hyphen? --ron |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
LOL... I was concentrating so on making sure I caught the 'st', 'nd' and
'rd' that I totally overlooked the mostly occurring 'th' suffix. Thanks for catching/noting that. For those still desiring to implement a formula of the form I originally posted, here is the corrected formula... =--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(M ID( A1,FIND(" ",A1,FIND("-",A1)+2),LEN(A1))),"rd",""),"nd",""),"st",""),"th" ,"") -- Rick (MVP - Excel) "Harlan Grove" wrote in message ... "Rick Rothstein" wrote... This formula will do what you want (providing there is always a space after the dash and there is no period after the year)... =--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(MID(A1, FIND(" ",A1,FIND("-",A1)+2),LEN(A1))),"rd",""),"nd",""),"st","") ... Your formula fails to delete the ordinal suffix "th", which, inconveniently, appears in the OP's example data, so your formula fails for the OP's sample data. Probably best to use regular expressions to do this. For example, using Laurent Longre's MOREFUNC.XLL add-in, you could use the formula =--REGEX.SUBSTITUTE(A1,"^[^-]*-\s*\S+\s+(\d+)[^ 0-9]*\s+(\S+)\s+(\d+).* $","[1]-[2]-[3]") |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
"Ashish Mathur" wrote...
You can also use the following array formula (Ctrl+Shift+Enter). *I have assumed that A14 holds Flight Schedule - Friday 28th November 2008 =MID(A14,MATCH(TRUE,ISNUMBER(1*MID(A14,ROW($1:$10 0),1)),0),50) .... If you're going to assume that all the OP's records end with the date and the named day of week always precedes the date, you could extract the date substring with a shorter formula. =MID(x,FIND("day ",x)+4,32) But your formula has one big potential flaw - the MATCH call locates the leftmost numeral. In the OP's only sample record that happens to be the beginning of the date substring, but the OP's actual data may contain nondate numerals to the left of the date substring, e.g., JFK to LAX Flight 999 - Tuesday 2nd Dec 2008 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
Ron Rosenfeld wrote...
.... All of the functional examples assume that there is a hyphen separating the date from the preceding text. * Will that always be the case? * And will there only be the one hyphen? So assume a more general approach is needed. Easiest using regular expressions. =REGEX.SUBSTITUTE(A1, ".*?\b(\d{1,2})(st|nd|rd|th)?\s+((Jan|Feb|Mar|Apr| May|Jun|Jul|Aug|Sep| Oct|Nov|Dec)\S*)\s+(\d{2,4})\b.*", "[1] [3] [5]") |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
On Sat, 29 Nov 2008 10:02:29 -0800 (PST), Harlan Grove
wrote: Ron Rosenfeld wrote... ... All of the functional examples assume that there is a hyphen separating the date from the preceding text. * Will that always be the case? * And will there only be the one hyphen? So assume a more general approach is needed. Easiest using regular expressions. =REGEX.SUBSTITUTE(A1, ".*?\b(\d{1,2})(st|nd|rd|th)?\s+((Jan|Feb|Mar|Apr |May|Jun|Jul|Aug|Sep| Oct|Nov|Dec)\S*)\s+(\d{2,4})\b.*", "[1] [3] [5]") I agree -- I came up with a similar regex: ..*?(\b\d{1,2})\D*?\s(\b(Jan|Feb|Mar|Apr|May|Jun|J ul|Aug|Sep|Oct|Nov|Dec)[a-z]*)\s+(\d{4}).* --ron |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
Ron Rosenfeld wrote...
On Sat, 29 Nov 2008 10:02:29 -0800 (PST), Harlan Grove .... ".*?\b(\d{1,2})(st|nd|rd|th)?\s+((Jan|Feb|Mar|Ap r|May|Jun|Jul|Aug|Sep| Oct|Nov|Dec)\S*)\s+(\d{2,4})\b.*", .... I agree -- I came up with a similar regex: .*?(\b\d{1,2})\D*?\s(\b(Jan|Feb|Mar|Apr|May|Jun|J ul|Aug|Sep|Oct|Nov|Dec)[a-z]*) \s+(\d{4}).* Minor quibble about \D*? vs (st|nd|rd|th)?. However, \d{4} for the year term doesn't allow for 2-digit years. Works for OP's single sample record, but it's not very general. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
On Sun, 30 Nov 2008 18:00:22 -0800 (PST), Harlan Grove
wrote: Ron Rosenfeld wrote... On Sat, 29 Nov 2008 10:02:29 -0800 (PST), Harlan Grove ... ".*?\b(\d{1,2})(st|nd|rd|th)?\s+((Jan|Feb|Mar|A pr|May|Jun|Jul|Aug|Sep| Oct|Nov|Dec)\S*)\s+(\d{2,4})\b.*", ... I agree -- I came up with a similar regex: .*?(\b\d{1,2})\D*?\s(\b(Jan|Feb|Mar|Apr|May|Jun| Jul|Aug|Sep|Oct|Nov|Dec)[a-z]*) \s+(\d{4}).* Minor quibble about \D*? vs (st|nd|rd|th)?. However, \d{4} for the year term doesn't allow for 2-digit years. Works for OP's single sample record, but it's not very general. I was wondering if you'd note that. I wondered about your ...|Dec)\S* in favor of ...|Dec)[a-z]* as a valid month name should only have letters following. And also, given how years are usually expressed, I'd prefer: (\d{2}|\d{4}) --ron |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
Ron Rosenfeld wrote...
.... I wondered about your ...|Dec)\S* in favor of ...|Dec)[a-z]* as a valid month name should only have letters following. Consider 1st Dec. 2008. Sometimes abbreviations include terminating periods. If I were going to be really fastidious, (Jan(uary|\.)?|Feb(ruary|\.)?| . . . |Dec(ember|\.)?) And also, given how years are usually expressed, I'd prefer: * (\d{2}|\d{4}) Believe it'd be more efficient to express that as (\d{2}){1,2}. |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
On Mon, 1 Dec 2008 09:07:18 -0800 (PST), Harlan Grove
wrote: Ron Rosenfeld wrote... ... I wondered about your ...|Dec)\S* in favor of ...|Dec)[a-z]* as a valid month name should only have letters following. Consider 1st Dec. 2008. Sometimes abbreviations include terminating periods. If I were going to be really fastidious, (Jan(uary|\.)?|Feb(ruary|\.)?| . . . |Dec(ember|\.)?) Good point. I suppose one could also use \s([0]\d|\d|[12]\d|3[01])\D :-) And also, given how years are usually expressed, I'd prefer: * (\d{2}|\d{4}) Believe it'd be more efficient to express that as (\d{2}){1,2}. I would have agreed. But, in doing some testing with a regex tool that has a debugging option (RegexBuddy by JGSoft), I discovered two things. 1. Mine should be (\d{4}|\d{2}) 2. For some reason, yours seems to take more steps to complete the match. Given 2008, mine (modified as above) matches in 2 steps, whereas yours seems to require 4 steps. Given 08, both require 3 steps --ron |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
Ron Rosenfeld wrote...
.... I suppose one could also use \s([0]\d|\d|[12]\d|3[01])\D Not specific enough - would allow 0 and 00 as valid day numbers. Better to use 0?[1-9] in place of 0\d|\d . 1. *Mine should be (\d{4}|\d{2}) .... What about \d\d(\d\d)? |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract date from text
On Mon, 1 Dec 2008 13:52:57 -0800 (PST), Harlan Grove
wrote: Ron Rosenfeld wrote... ... I suppose one could also use \s([0]\d|\d|[12]\d|3[01])\D Not specific enough - would allow 0 and 00 as valid day numbers. Better to use 0?[1-9] in place of 0\d|\d . Agree 1. *Mine should be (\d{4}|\d{2}) ... What about \d\d(\d\d)? According to the RegexBuddy debugger, that takes 4 steps to match 08 and 5 steps to match 2008 --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extract date from string | Excel Worksheet Functions | |||
Extract Date | Excel Worksheet Functions | |||
extract date from the most current date | Excel Discussion (Misc queries) | |||
How do I extract a date as text not the 1900 reference number | Excel Discussion (Misc queries) | |||
EXTRACT TEXT FROM A DATE | Excel Worksheet Functions |