Computed value returning#VALUE

  • Thread starter Thread starter Kennedy
  • Start date Start date
K

Kennedy

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

=IF(U3="","",TRIM(MID(SUBSTITUTE(U3,",",REPT("
",255),2),FIND(",",U3)+1,255)))
 
=TRIM(MID(SUBSTITUTE(U3,",",REPT(" ",255),2),FIND(",",U3)+1,255))
You had introduced commas and extra spaces in between "".
Click to expand...

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="","",........
 
Thanks to both of you. Both worked well. Going to use the one that T. Valko
submitted.
Again...THANK YOU both...geniuses!
 
Back
Top