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