I've been offline for a few days thanks to the big snow storm!

I kind of had a feeling there were some whitespace characters causing this
problem.

To the OP...

If this data is copied/pasted/imported from a website or from another
application, char(160) problems are very common.

I copy/paste/import from the web just about every day. To eliminate the
char(160) problem I use this macro by David McRitchie:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Saves a ton of time and aggravation!

--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
You are welcome. Glad we could finally get it working.

Steve wrote:
Thank you so very, very much. I really appreciate all your patience.
I don't think I even want to begin to know what a non-normal space is,
but the final formula works great.

Thank you again,

Steve

"Glenn" wrote:

The character after the colon is not a "normal" space. I copied the
text from A3 into A1 of a new worksheet, then entered the following two
formulas:


B1 = MID($A$1,ROW(),1)
C1 = CODE(MID($A$1,ROW(),1))


I copied B1 and C1 down the columns until the entire string was
evaluated. The spaces between words ("Analysis" and "Period",
"Processing" and "Days") is CHAR(32), all other spaces are CHAR(160).

Try this:

=--TRIM(RIGHT(SUBSTITUTE(A3,CHAR(160),REPT(" ",10)),10))


Steve wrote:
Thank you very much.

It's on it's way.

Steve

"Glenn" wrote:

Can not reproduce the error with information you are providing. If
you like, email me a *SMALL* sample worksheet with the error and I'll
take a look at it. Send to glennschwandt at yahoo dot com.

Steve wrote:
#VALUE! -
=--TRIM(RIGHT(SUBSTITUTE(TRIM(A3),":",REPT(" ",10)),10))
The above is the data in the #Value! cell via the formula bar.
"Glenn" wrote:

Copy the value from A3 (the one that results in #Value!) directly
into a response to this post. Do not re-type or edit in any way.

Steve wrote:
This =--TRIM(RIGHT(SUBSTITUTE(TRIM(A3),":",REPT(" ",10)),10))
Gives me a #Value!
Also isnumber for that cell is false.

Thanks,

Steve

.

.

.