Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Computed value returning#VALUE
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Computed value returning#VALUE
Try this...
=IF(U3="","",TRIM(MID(SUBSTITUTE(U3,",",REPT(" ",255),2),FIND(",",U3)+1,255))) -- Biff Microsoft Excel MVP "Kennedy" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Computed value returning#VALUE
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Computed value returning#VALUE
=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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Computed value returning#VALUE
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" wrote in message ... 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. . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Computed value returning#VALUE
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Kennedy" wrote in message ... 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" wrote in message ... 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
computed named cell references | New Users to Excel | |||
cell values being computed | Excel Worksheet Functions | |||
Change the way a non-computed percentage is displayed | Excel Discussion (Misc queries) | |||
VLOOKUP - computed go to | Excel Worksheet Functions | |||
Advanced Filtering - Computed Criteria | Excel Worksheet Functions |