Prev: how do i find out what data is hidden i excel
Next: Adding and then sorting data into an already existing Excel 2007 w
From: Ron on 26 Mar 2010 15:45
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" <JenT(a)discussions.microsoft.com> wrote in message
> news:24E6D3D1-892D-4A59-8BD4-BFA191F63ED4(a)microsoft.com...
> > 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
>
>
> .
>
From: Jen_T on 26 Mar 2010 15:45

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" <JenT(a)discussions.microsoft.com> wrote in message
> news:24E6D3D1-892D-4A59-8BD4-BFA191F63ED4(a)microsoft.com...
> > 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
>
>
> .
>
From: Ron on 26 Mar 2010 15:49
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" <JenT(a)discussions.microsoft.com> wrote in message
> news:24E6D3D1-892D-4A59-8BD4-BFA191F63ED4(a)microsoft.com...
> > 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
>
>
> .
>
From: T. Valko on 26 Mar 2010 16:57
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(a)Buy" <RonBuy(a)discussions.microsoft.com> wrote in message
news:C438006C-56D8-453E-BA70-46BEFEA2F2F4(a)microsoft.com...
> Brilliant Biff
>
> "T. Valko" wrote:
>
>> Try this...
>>
>> =LOOKUP(1E100,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW(INDIRECT("1:"&LEN(A2)))))
>>
>> Format as Date
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Jen_T" <JenT(a)discussions.microsoft.com> wrote in message
>> news:24E6D3D1-892D-4A59-8BD4-BFA191F63ED4(a)microsoft.com...
>> > 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
>>
>>
>> .
>>


From: T. Valko on 26 Mar 2010 21:56
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" <biffinpitt(a)comcast.net> wrote in message
news:OyEGsbSzKHA.928(a)TK2MSFTNGP05.phx.gbl...
> 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(a)Buy" <RonBuy(a)discussions.microsoft.com> wrote in message
> news:C438006C-56D8-453E-BA70-46BEFEA2F2F4(a)microsoft.com...
>> Brilliant Biff
>>
>> "T. Valko" wrote:
>>
>>> Try this...
>>>
>>> =LOOKUP(1E100,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW(INDIRECT("1:"&LEN(A2)))))
>>>
>>> Format as Date
>>>
>>> --
>>> Biff
>>> Microsoft Excel MVP
>>>
>>>
>>> "Jen_T" <JenT(a)discussions.microsoft.com> wrote in message
>>> news:24E6D3D1-892D-4A59-8BD4-BFA191F63ED4(a)microsoft.com...
>>> > 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
>>>
>>>
>>> .
>>>
>
>


First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: how do i find out what data is hidden i excel
Next: Adding and then sorting data into an already existing Excel 2007 w