Convert variable #'s in a gen. format to a # that can be used w/ma

[Steve]

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

Thanks,

Steve

Gives me a

 

[Glenn]

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]

Analysis Period: 01/23/2010 to 01/29/2010 - Processing Days: 6

 

[Steve]

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

 

[Glenn]

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]

Thank you very much.

It's on it's way.

Steve

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.

.

 

[Glenn]

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]

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]

You are welcome. Glad we could finally get it working.

 

[T. Valko]

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!