|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
March 26th, 2010, 06:35 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Function
If a cell has text and numeric (date) is there a way to pull the date out
easily ? E.g. Product dropped off on 11/01/2009 by Michelle Smith I would like to see 11/01/2009 Thank you 
|
|
#2
March 26th, 2010, 06:50 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
Try this...
=LOOKUP(1E100,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"01234567 89")),ROW(INDIRECT("1:"&LEN(A2))))) Format as Date -- Biff Microsoft Excel MVP "Jen_T" wrote in message ... If a cell has text and numeric (date) is there a way to pull the date out easily ? E.g. Product dropped off on 11/01/2009 by Michelle Smith I would like to see 11/01/2009 Thank you
|
|
#3
March 26th, 2010, 06:53 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
=DATEVALUE(MID(A2,FIND("/",A2)-2,10))
Format as date. -- Best Regards, Luke M "Jen_T" wrote in message ... If a cell has text and numeric (date) is there a way to pull the date out easily ? E.g. Product dropped off on 11/01/2009 by Michelle Smith I would like to see 11/01/2009 Thank you
|
|
#4
March 26th, 2010, 07:27 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
Here's some possible leads:
http://office.microsoft.com/en-us/ex...549011033.aspx http://www.meadinkent.co.uk/xlextracttext.htm "Jen_T" wrote: If a cell has text and numeric (date) is there a way to pull the date out easily ? E.g. Product dropped off on 11/01/2009 by Michelle Smith I would like to see 11/01/2009 Thank you
|
|
#5
March 26th, 2010, 07:31 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
Brilliant Biff
"T. Valko" wrote: Try this... =LOOKUP(1E100,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"01234567 89")),ROW(INDIRECT("1:"&LEN(A2))))) Format as Date -- Biff Microsoft Excel MVP "Jen_T" wrote in message ... If a cell has text and numeric (date) is there a way to pull the date out easily ? E.g. Product dropped off on 11/01/2009 by Michelle Smith I would like to see 11/01/2009 Thank you .
|
|
#6
March 26th, 2010, 07:45 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
Luke,
Very exceptable unless there are no other numbers in the sentence e.g. Product no. 1 dropped off on 11/01/2009 by Michelle Smith "Luke M" wrote: =DATEVALUE(MID(A2,FIND("/",A2)-2,10)) Format as date. -- Best Regards, Luke M "Jen_T" wrote in message ... If a cell has text and numeric (date) is there a way to pull the date out easily ? E.g. Product dropped off on 11/01/2009 by Michelle Smith I would like to see 11/01/2009 Thank you .
|
|
#7
March 26th, 2010, 07:45 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
Thank you, Luke, How does one read the formula ? I do not quite understand how this one works. But it worked beautifully, can you explain ? Thank you "Luke M" wrote: =DATEVALUE(MID(A2,FIND("/",A2)-2,10)) Format as date. -- Best Regards, Luke M "Jen_T" wrote in message ... If a cell has text and numeric (date) is there a way to pull the date out easily ? E.g. Product dropped off on 11/01/2009 by Michelle Smith I would like to see 11/01/2009 Thank you .
|
|
#8
March 26th, 2010, 07:49 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
I'll try again:
Luke, Very axceptable unless there are other numbers in the sentence e.g. Product no. 1 dropped off on 11/01/2009 by Michelle Smith "Luke M" wrote: =DATEVALUE(MID(A2,FIND("/",A2)-2,10)) Format as date. -- Best Regards, Luke M "Jen_T" wrote in message ... If a cell has text and numeric (date) is there a way to pull the date out easily ? E.g. Product dropped off on 11/01/2009 by Michelle Smith I would like to see 11/01/2009 Thank you .
|
|
#9
March 26th, 2010, 08:57 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
Thanks!
But, I think I'm using an atomic bomb to kill an ant! That formula is a generic formula to extract a number from a string. I like Luke's suggestion but it needs tweaked a bit. Let's assume that there are no other numbers in the string and the date is *always* in the format m/d/yyyy or m/dd/yyyy. Luke's formula could fail when the date is at the start of the string. Consider these strings: 1/1/2010 is the deadline 1/10/2010 is the deadline 10/1/2010 is the deadline 10/10/2010 is the deadline The deadline is 1/1/2010 The deadline is 1/10/2011 The deadline is 10/1/2010 The deadline is 10/10/2010 The deadline of 1/1/2010 is firm The deadline of 1/10/2010 is firm The deadline of 10/1/2010 is firm The deadline of 10/10/2010 is firm So, Luke's formula with a tweak will account for all of the above: =--TRIM(MID(" "&A1,FIND("/"," "&A1)-2,10)) -- Biff Microsoft Excel MVP "Ron@Buy" wrote in message ... Brilliant Biff "T. Valko" wrote: Try this... =LOOKUP(1E100,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"01234567 89")),ROW(INDIRECT("1:"&LEN(A2))))) Format as Date -- Biff Microsoft Excel MVP "Jen_T" wrote in message ... If a cell has text and numeric (date) is there a way to pull the date out easily ? E.g. Product dropped off on 11/01/2009 by Michelle Smith I would like to see 11/01/2009 Thank you .
|
|
#10
March 27th, 2010, 01:56 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
Well, this formula has a potential flaw *if* the date is followed by another
character like a punctuation mark. Maybe the "atomic option" is best afterall. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Thanks! But, I think I'm using an atomic bomb to kill an ant! That formula is a generic formula to extract a number from a string. I like Luke's suggestion but it needs tweaked a bit. Let's assume that there are no other numbers in the string and the date is *always* in the format m/d/yyyy or m/dd/yyyy. Luke's formula could fail when the date is at the start of the string. Consider these strings: 1/1/2010 is the deadline 1/10/2010 is the deadline 10/1/2010 is the deadline 10/10/2010 is the deadline The deadline is 1/1/2010 The deadline is 1/10/2011 The deadline is 10/1/2010 The deadline is 10/10/2010 The deadline of 1/1/2010 is firm The deadline of 1/10/2010 is firm The deadline of 10/1/2010 is firm The deadline of 10/10/2010 is firm So, Luke's formula with a tweak will account for all of the above: =--TRIM(MID(" "&A1,FIND("/"," "&A1)-2,10)) -- Biff Microsoft Excel MVP "Ron@Buy" wrote in message ... Brilliant Biff "T. Valko" wrote: Try this... =LOOKUP(1E100,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"01234567 89")),ROW(INDIRECT("1:"&LEN(A2))))) Format as Date -- Biff Microsoft Excel MVP "Jen_T" wrote in message ... If a cell has text and numeric (date) is there a way to pull the date out easily ? E.g. Product dropped off on 11/01/2009 by Michelle Smith I would like to see 11/01/2009 Thank you .
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
