Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find replace the 1st character in a cell | Excel Discussion (Misc queries) | |||
find a replace character question | Excel Discussion (Misc queries) | |||
find a character and replace with a command in Excel | Excel Discussion (Misc queries) | |||
Find 1 word and replace it and leave the leading zero | Excel Discussion (Misc queries) | |||
How do you find and replace a Wildcard character in Excel? | Excel Discussion (Misc queries) |