Computed value returning#VALUE
in [General]
|
Prev: Forumla List
Next: Excel 2007 - freeze multiple panes?
From: Kennedy on 23 Mar 2010 12:50 I have a computed value =TRIM(MID(SUBSTITUTE(U3,",",REPT(" ",255),2),FIND(",",U3)+1,255)) that pulls the date out of a string of text. In this case, the date is returned because their is data in U3. However, when I have columns that do not have data, it returns a #VALUE. Is there a way to get around this. The column being referenced is also a computed value, so I am wondering if that is the case.
From: T. Valko on 23 Mar 2010 12:59 Try this... =IF(U3="","",TRIM(MID(SUBSTITUTE(U3,",",REPT(" ",255),2),FIND(",",U3)+1,255))) -- Biff Microsoft Excel MVP "Kennedy" <Kennedy(a)discussions.microsoft.com> wrote in message news:9F211580-0981-48E5-B282-497389B77F5D(a)microsoft.com... >I have a computed value > =TRIM(MID(SUBSTITUTE(U3,",",REPT(" ",255),2),FIND(",",U3)+1,255)) > that pulls the date out of a string of text. In this case, the date is > returned because their is data in U3. However, when I have columns that do > not have data, it returns a #VALUE. Is there a way to get around this. The > column being referenced is also a computed value, so I am wondering if > that > is the case.
From: Russell Dawson on 23 Mar 2010 13:28 Try this =TRIM(MID(SUBSTITUTE(U3,"",REPT("",255),2),FIND("",U3)+1,255)) You had introduced commas and extra spaces in between "". -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Kennedy" wrote: > I have a computed value > =TRIM(MID(SUBSTITUTE(U3,",",REPT(" ",255),2),FIND(",",U3)+1,255)) > that pulls the date out of a string of text. In this case, the date is > returned because their is data in U3. However, when I have columns that do > not have data, it returns a #VALUE. Is there a way to get around this. The > column being referenced is also a computed value, so I am wondering if that > is the case.
From: T. Valko on 23 Mar 2010 13:47 >=TRIM(MID(SUBSTITUTE(U3,",",REPT(" ",255),2),FIND(",",U3)+1,255)) >You had introduced commas and extra spaces in between "". No, the formula is correct. They're extracting the substring that is between 2 commas. Something like this: text, date, more_text The formula as written extracts "date". My interpretation of the post is when the cell is empty then FIND will return the error #VALUE!. So we need to test that the cell is not empty: =IF(cell_ref="","",........ -- Biff Microsoft Excel MVP "Russell Dawson" <russell.dawson(a)discussions.microsoft.com> wrote in message news:DA4B539C-EB7D-4E95-9E40-94178A5D666B(a)microsoft.com... > Try this > > =TRIM(MID(SUBSTITUTE(U3,"",REPT("",255),2),FIND("",U3)+1,255)) > > You had introduced commas and extra spaces in between "". > -- > Russell Dawson > Excel Student > > Please hit "Yes" if this post was helpful. > > > "Kennedy" wrote: > >> I have a computed value >> =TRIM(MID(SUBSTITUTE(U3,",",REPT(" ",255),2),FIND(",",U3)+1,255)) >> that pulls the date out of a string of text. In this case, the date is >> returned because their is data in U3. However, when I have columns that >> do >> not have data, it returns a #VALUE. Is there a way to get around this. >> The >> column being referenced is also a computed value, so I am wondering if >> that >> is the case.
From: Kennedy on 23 Mar 2010 14:10
Thanks to both of you. Both worked well. Going to use the one that T. Valko submitted. Again...THANK YOU both...geniuses! "T. Valko" wrote: > Try this... > > =IF(U3="","",TRIM(MID(SUBSTITUTE(U3,",",REPT(" > ",255),2),FIND(",",U3)+1,255))) > > -- > Biff > Microsoft Excel MVP > > > "Kennedy" <Kennedy(a)discussions.microsoft.com> wrote in message > news:9F211580-0981-48E5-B282-497389B77F5D(a)microsoft.com... > >I have a computed value > > =TRIM(MID(SUBSTITUTE(U3,",",REPT(" ",255),2),FIND(",",U3)+1,255)) > > that pulls the date out of a string of text. In this case, the date is > > returned because their is data in U3. However, when I have columns that do > > not have data, it returns a #VALUE. Is there a way to get around this. The > > column being referenced is also a computed value, so I am wondering if > > that > > is the case. > > > . > |