Was the text imported into Excel?

Copying your data, I got the same error as the text string for DATEVALUE had
an extra character [non-viewable] on the end.

using this formula, I got the date returned:

=DATEVALUE((TRIM(MID(SUBSTITUTE(A1,CHAR(160)," "),FIND("-",A1)+3,11))))

This suggests are are "hidden" ("char(160)") characters in the string which
the formula substitutes with blank and hence TRIM works.

HTH

"hmm" wrote:

I have in cell A25 the text "- 6/19/2007 6:22:12 PM". The formula

=DATEVALUE((TRIM(MID(A25,FIND("-",A25)+3,11))))

is giving me a #VALUE! error.

How can I get it to return the correct date?