more character 160 leading/trailing find/replace problems

[M]

WHAT Formula will return a number for:
Examples: cell contents of A1 = 15,869.00
A2 = empty
"Cell View" shows: a leading and trailing character 160 in all cells of
interest including otherwise empty or blank cells.
IsNumber shows FALSE for numbers in these cells
No error button appears when these cells are selected
Numbers are not converted using procedures which otherwise work
Edit Find/Replace for find ALT 0160 returns error msg that no matching data
could be found

I can’t seem to convert or access or reference the number contained in the
cell as a “numberâ€
EXCEPT – Once and I can’t remember or repeat how I accomplished that
(accident?)

I would like to use = (reference number containing cell with leading and
trailing 160 characters) and return a “real†number – nothing I have tried so
far works.

M

 

[T. Valko]

Try this macro:

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

Install the macro
Select the range of cells you want to "clean"
Run the macro

 

[Ron Rosenfeld]

WHAT Formula will return a number for:
Examples: cell contents of A1 = 15,869.00
A2 = empty
"Cell View" shows: a leading and trailing character 160 in all cells of
interest including otherwise empty or blank cells.
IsNumber shows FALSE for numbers in these cells
No error button appears when these cells are selected
Numbers are not converted using procedures which otherwise work
Edit Find/Replace for find ALT 0160 returns error msg that no matching data
could be found

I can’t seem to convert or access or reference the number contained in the
cell as a “number”
EXCEPT – Once and I can’t remember or repeat how I accomplished that
(accident?)

I would like to use = (reference number containing cell with leading and
trailing 160 characters) and return a “real” number – nothing I have tried so
far works.

M

=--SUBSTITUTE(A1,CHAR(160),"")

--ron

 

[M]

Ron !!!!!!
IT WORKED !!!! I was "given up" - simple solution
THANK YOU !!
M

 

[M]

T. Valko
THANKS ever so much for the macro -
these really make all the difference in the world
Again Thanks,
M

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

T. Valko
THANKS ever so much for the macro -
these really make all the difference in the world
Again Thanks,
M

 

[Ron Rosenfeld]

Ron !!!!!!
IT WORKED !!!! I was "given up" - simple solution
THANK YOU !!
M

You're welcome. Glad to help. Thanks for the feedback.
--ron