more character 160 leading/trailing find/replace problems
 

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 cant seem to convert or access or reference the number contained in the
cell as a €œnumber€�
EXCEPT €“ Once and I cant 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

more character 160 leading/trailing find/replace problems
 

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

--
Biff
Microsoft Excel MVP


"M" wrote in message
...
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

more character 160 leading/trailing find/replace problems
 

On Thu, 30 Oct 2008 18:28:02 -0700, M wrote:

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

more character 160 leading/trailing find/replace problems
 

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

"Ron Rosenfeld" wrote:

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

more character 160 leading/trailing find/replace problems
 

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

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

more character 160 leading/trailing find/replace problems
 

You're welcome!

--
Biff
Microsoft Excel MVP


"M" wrote in message
...

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

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

more character 160 leading/trailing find/replace problems
 

On Thu, 30 Oct 2008 19:47:02 -0700, M wrote:

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

"Ron Rosenfeld" wrote:

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


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