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 |