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.

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.

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.

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.

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.


.

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.


.