| Home |
| Search |
| Today's Posts |
|
#1
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
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
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
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
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
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
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
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
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
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 . |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
Here is a slightly different "atomic option" from the one you posted which
does not contain the Volatile INDIRECT function call (plus it's 2 characters shorter<g)... =LOOKUP(1e100,--LEFT(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"012 3456789")),LEN(A2)),ROW($1:$99))) -- Rick (MVP - Excel) "T. Valko" wrote in message ... 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 . |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
does not contain the Volatile INDIRECT function
ROW($1:$99) Well, you can either use the volatile INDIRECT and be robust against row insertions or you can use ROW and hope you don't ever need to insert rows from row 1 to 99. Of course, if the date is *always* in a specific format where the length of the date string is *always* the same then it could be as simple as: =--MID(A1,FIND("/",A1)-n1,n2) Where n1 = 1 or 2, the length of the month portion of the date and n2 = the total length of the date string. This would be very easy if there was a SUBSTITUTE / REPLACE type function that would take arrays as the old_text argument! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... Here is a slightly different "atomic option" from the one you posted which does not contain the Volatile INDIRECT function call (plus it's 2 characters shorter<g)... =LOOKUP(1e100,--LEFT(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"012 3456789")),LEN(A2)),ROW($1:$99))) -- Rick (MVP - Excel) "T. Valko" wrote in message ... 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 . |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
Insertions do not seem to affect either of our formulas as far as I can see.
-- Rick (MVP - Excel) "T. Valko" wrote in message ... does not contain the Volatile INDIRECT function ROW($1:$99) Well, you can either use the volatile INDIRECT and be robust against row insertions or you can use ROW and hope you don't ever need to insert rows from row 1 to 99. Of course, if the date is *always* in a specific format where the length of the date string is *always* the same then it could be as simple as: =--MID(A1,FIND("/",A1)-n1,n2) Where n1 = 1 or 2, the length of the month portion of the date and n2 = the total length of the date string. This would be very easy if there was a SUBSTITUTE / REPLACE type function that would take arrays as the old_text argument! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... Here is a slightly different "atomic option" from the one you posted which does not contain the Volatile INDIRECT function call (plus it's 2 characters shorter<g)... =LOOKUP(1e100,--LEFT(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"012 3456789")),LEN(A2)),ROW($1:$99))) -- Rick (MVP - Excel) "T. Valko" wrote in message ... 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 . |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
A1 = 1/1/2009 is the start date.
It might be far-fetched but if you inserted 9 or more new rows at the top of the sheet then ROW($1:$99) becomes ROW($10:$108) etc. Then LEFT starts with 10 characters and the date portion by itself is never evaluated. -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... Insertions do not seem to affect either of our formulas as far as I can see. -- Rick (MVP - Excel) "T. Valko" wrote in message ... does not contain the Volatile INDIRECT function ROW($1:$99) Well, you can either use the volatile INDIRECT and be robust against row insertions or you can use ROW and hope you don't ever need to insert rows from row 1 to 99. Of course, if the date is *always* in a specific format where the length of the date string is *always* the same then it could be as simple as: =--MID(A1,FIND("/",A1)-n1,n2) Where n1 = 1 or 2, the length of the month portion of the date and n2 = the total length of the date string. This would be very easy if there was a SUBSTITUTE / REPLACE type function that would take arrays as the old_text argument! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... Here is a slightly different "atomic option" from the one you posted which does not contain the Volatile INDIRECT function call (plus it's 2 characters shorter<g)... =LOOKUP(1e100,--LEFT(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"012 3456789")),LEN(A2)),ROW($1:$99))) -- Rick (MVP - Excel) "T. Valko" wrote in message ... 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 . |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Function
Okay, I see what you mean.
-- Rick (MVP - Excel) "T. Valko" wrote in message ... A1 = 1/1/2009 is the start date. It might be far-fetched but if you inserted 9 or more new rows at the top of the sheet then ROW($1:$99) becomes ROW($10:$108) etc. Then LEFT starts with 10 characters and the date portion by itself is never evaluated. -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... Insertions do not seem to affect either of our formulas as far as I can see. -- Rick (MVP - Excel) "T. Valko" wrote in message ... does not contain the Volatile INDIRECT function ROW($1:$99) Well, you can either use the volatile INDIRECT and be robust against row insertions or you can use ROW and hope you don't ever need to insert rows from row 1 to 99. Of course, if the date is *always* in a specific format where the length of the date string is *always* the same then it could be as simple as: =--MID(A1,FIND("/",A1)-n1,n2) Where n1 = 1 or 2, the length of the month portion of the date and n2 = the total length of the date string. This would be very easy if there was a SUBSTITUTE / REPLACE type function that would take arrays as the old_text argument! -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... Here is a slightly different "atomic option" from the one you posted which does not contain the Volatile INDIRECT function call (plus it's 2 characters shorter<g)... =LOOKUP(1e100,--LEFT(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"012 3456789")),LEN(A2)),ROW($1:$99))) -- Rick (MVP - Excel) "T. Valko" wrote in message ... 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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Function to lookup date on tab in excel and populate date on calen | Excel Worksheet Functions | |||
| Difference betwen Excel Date () Function and System Date | Excel Worksheet Functions | |||
| Difference System date and Excel Date function | Excel Worksheet Functions | |||
| Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
| Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions | |||
Linear Mode
