|
spaces not recognized as spaces
I'm seeking to analyze data copied from stock broker on-line statement.
The values copy across as text with (what appears to be) a space before and after each number. I can manually delete the spaces by pressing F2 and the delete or backspace key as appropriate for the space at the front and rear of the number which is otherwise of the form -$123,456.00 The function Value() does not retrieve the value from the text stream. The function Trim() does not remove the spaces. The menu function, "replace" does not find the spaces when you use the space bar to enter the item to be searched for. Is there a special character that appears as a space but is not a space? |
spaces not recognized as spaces
Perhaps the "spaces" are really html non-breaking spaces.
Try something like this: From the Excel main menu: <edit<replace Find what: Hold down the [Alt] key....type 0160...release the [Alt] key Replace with: (leave this blank) Click the [Replace All] button Does that help? *********** Regards, Ron XL2002, WinXP "windsurferLA" wrote: I'm seeking to analyze data copied from stock broker on-line statement. The values copy across as text with (what appears to be) a space before and after each number. I can manually delete the spaces by pressing F2 and the delete or backspace key as appropriate for the space at the front and rear of the number which is otherwise of the form -$123,456.00 The function Value() does not retrieve the value from the text stream. The function Trim() does not remove the spaces. The menu function, "replace" does not find the spaces when you use the space bar to enter the item to be searched for. Is there a special character that appears as a space but is not a space? |
spaces not recognized as spaces
Most likely you have invisible html characters, try this
do editreplace, in the find what box hold down alt key and type 0160 on the numpad, release the alt button, leave replace with empty click OK If you do this often you might want to use a macro http://www.mvps.org/dmcritchie/excel/join.htm#trimall same web site has info on how to install macros -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "windsurferLA" wrote in message . .. I'm seeking to analyze data copied from stock broker on-line statement. The values copy across as text with (what appears to be) a space before and after each number. I can manually delete the spaces by pressing F2 and the delete or backspace key as appropriate for the space at the front and rear of the number which is otherwise of the form -$123,456.00 The function Value() does not retrieve the value from the text stream. The function Trim() does not remove the spaces. The menu function, "replace" does not find the spaces when you use the space bar to enter the item to be searched for. Is there a special character that appears as a space but is not a space? |
spaces not recognized as spaces
Thanks for super fast response by approach didnt work. I forgot to say
Im running XL97. The replace or find would not accept any keyboard entries while the alt-key way displayed. The machine beeps. I cant enter 0160 while the alt key is depressed. Your approach seems to seek to enter the ASCII code for a special symbol. I had been thinking along similar lines. I tried ^s to mimic the code one uses for paragraph ^p and tab ^t but it also did not work. . Any other ideas??? My immediate problem has been solved by my manually editing the 60 numbers, but Id like to know what is going on for future reference. Ron Coderre wrote: Perhaps the "spaces" are really html non-breaking spaces. Try something like this: From the Excel main menu: <edit<replace Find what: Hold down the [Alt] key....type 0160...release the [Alt] key Replace with: (leave this blank) Click the [Replace All] button Does that help? *********** Regards, Ron XL2002, WinXP "windsurferLA" wrote: I'm seeking to analyze data copied from stock broker on-line statement. The values copy across as text with (what appears to be) a space before and after each number. I can manually delete the spaces by pressing F2 and the delete or backspace key as appropriate for the space at the front and rear of the number which is otherwise of the form -$123,456.00 The function Value() does not retrieve the value from the text stream. The function Trim() does not remove the spaces. The menu function, "replace" does not find the spaces when you use the space bar to enter the item to be searched for. Is there a special character that appears as a space but is not a space? |
spaces not recognized as spaces
Use the NumPad when entering the ALT + 0160
The regular number keys don't work for this method. Gord Dibben MS Excel MVP On Wed, 26 Jul 2006 10:42:39 -0700, windsurferLA wrote: Thanks for super fast response by approach didnt work. I forgot to say Im running XL97. The replace or find would not accept any keyboard entries while the alt-key way displayed. The machine beeps. I cant enter 0160 while the alt key is depressed. Your approach seems to seek to enter the ASCII code for a special symbol. I had been thinking along similar lines. I tried ^s to mimic the code one uses for paragraph ^p and tab ^t but it also did not work. . Any other ideas??? My immediate problem has been solved by my manually editing the 60 numbers, but Id like to know what is going on for future reference. Ron Coderre wrote: Perhaps the "spaces" are really html non-breaking spaces. Try something like this: From the Excel main menu: <edit<replace Find what: Hold down the [Alt] key....type 0160...release the [Alt] key Replace with: (leave this blank) Click the [Replace All] button Does that help? *********** Regards, Ron XL2002, WinXP "windsurferLA" wrote: I'm seeking to analyze data copied from stock broker on-line statement. The values copy across as text with (what appears to be) a space before and after each number. I can manually delete the spaces by pressing F2 and the delete or backspace key as appropriate for the space at the front and rear of the number which is otherwise of the form -$123,456.00 The function Value() does not retrieve the value from the text stream. The function Trim() does not remove the spaces. The menu function, "replace" does not find the spaces when you use the space bar to enter the item to be searched for. Is there a special character that appears as a space but is not a space? |
spaces not recognized as spaces
Thanks for super fast response by approach didnt work. I forgot to say
Im running XL97. The replace or find would not accept any keyboard entries while the alt-key way displayed. The machine beeps. I cant enter 0160 while the alt key is depressed. Perhaps the ability to accept ASCII codes is a feature added after XL97. Your approach seems to seek to enter the ASCII code for a special symbol. I had been thinking along similar lines. I tried ^s to mimic the code one uses for paragraph ^p and tab ^t but it also did not work. . The CLEAN() function also did not work, but now I understand that CLEAN() does not work on ASCII 160. I note also that if I copy the content of the cell into Notepad, there appears to be a space before and after the number. If I then copy the string back from Notepad into Excel, Excel still does not find the space. I would think that Notepad would show something other than a space if it was some special ASCII code. I have been reading http://www.mvps.org/dmcritchie/excel...tm#debugformat suggested by another. It suggested using CODE() to find out the ASCII. When I used it, it returned 160. Thus you are probably right on the character being the html non-breaking space. Another suggestion to press F2 and then ENTER does not seem to change the nature of the special character. At this point, the issue has become an intellectual challenge. My immediate problem was solved by my manually editing the 45 individual numbers. However, exploring the problem has sensitized me to potential issues. HOWEVER, ANY OTHER IDEAS SHORT OF UPGRADING TO A NEWER VERSION OF XL WILL BE EXPLORED??? Ron Coderre wrote: Perhaps the "spaces" are really html non-breaking spaces. Try something like this: From the Excel main menu: <edit<replace Find what: Hold down the [Alt] key....type 0160...release the [Alt] key Replace with: (leave this blank) Click the [Replace All] button Does that help? *********** Regards, Ron XL2002, WinXP "windsurferLA" wrote: I'm seeking to analyze data copied from stock broker on-line statement. The values copy across as text with (what appears to be) a space before and after each number. I can manually delete the spaces by pressing F2 and the delete or backspace key as appropriate for the space at the front and rear of the number which is otherwise of the form -$123,456.00 The function Value() does not retrieve the value from the text stream. The function Trim() does not remove the spaces. The menu function, "replace" does not find the spaces when you use the space bar to enter the item to be searched for. Is there a special character that appears as a space but is not a space? |
spaces not recognized as spaces
WE GOT A HIT!!!! I learned yet another thing... yes using number pad
allows data entry into FIND of ASCII Char() . with number pad, entering 160 produces character that looks like an "a" which my DOS book says is char(160) . Seems logical. At first, when I entered 0160 into replace, I thought it didn't work. then I noted that the was a blank space in the find field of the find and replace dialog box. I then hit "replace all" and immediate all the mysterious spaces were gone. THANKS Gord Dibben wrote: Use the NumPad when entering the ALT + 0160 The regular number keys don't work for this method. Gord Dibben MS Excel MVP On Wed, 26 Jul 2006 10:42:39 -0700, windsurferLA wrote: Thanks for super fast response by approach didnt work. I forgot to say Im running XL97. The replace or find would not accept any keyboard entries while the alt-key way displayed. The machine beeps. I cant enter 0160 while the alt key is depressed. Your approach seems to seek to enter the ASCII code for a special symbol. I had been thinking along similar lines. I tried ^s to mimic the code one uses for paragraph ^p and tab ^t but it also did not work. . Any other ideas??? My immediate problem has been solved by my manually editing the 60 numbers, but Id like to know what is going on for future reference. Ron Coderre wrote: Perhaps the "spaces" are really html non-breaking spaces. Try something like this: From the Excel main menu: <edit<replace Find what: Hold down the [Alt] key....type 0160...release the [Alt] key Replace with: (leave this blank) Click the [Replace All] button Does that help? *********** Regards, Ron XL2002, WinXP "windsurferLA" wrote: I'm seeking to analyze data copied from stock broker on-line statement. The values copy across as text with (what appears to be) a space before and after each number. I can manually delete the spaces by pressing F2 and the delete or backspace key as appropriate for the space at the front and rear of the number which is otherwise of the form -$123,456.00 The function Value() does not retrieve the value from the text stream. The function Trim() does not remove the spaces. The menu function, "replace" does not find the spaces when you use the space bar to enter the item to be searched for. Is there a special character that appears as a space but is not a space? |
spaces not recognized as spaces
WE GOT A HIT!!!! I learned yet another thing... yes using number pad
allows data entry into FIND of ASCII Char() . with number pad, entering 160 produces character that looks like an "a" which my DOS book says is char(160) . Seems logical. At first, when I entered 0160 into replace, I thought it didn't work. then I noted that the was a blank space in the find field of the find and replace dialog box. I then hit "replace all" and immediate all the mysterious spaces were gone. THANKS windsurferLA wrote: Thanks for super fast response by approach didnt work. I forgot to say Im running XL97. The replace or find would not accept any keyboard entries while the alt-key way displayed. The machine beeps. I cant enter 0160 while the alt key is depressed. Perhaps the ability to accept ASCII codes is a feature added after XL97. Your approach seems to seek to enter the ASCII code for a special symbol. I had been thinking along similar lines. I tried ^s to mimic the code one uses for paragraph ^p and tab ^t but it also did not work. . The CLEAN() function also did not work, but now I understand that CLEAN() does not work on ASCII 160. I note also that if I copy the content of the cell into Notepad, there appears to be a space before and after the number. If I then copy the string back from Notepad into Excel, Excel still does not find the space. I would think that Notepad would show something other than a space if it was some special ASCII code. I have been reading http://www.mvps.org/dmcritchie/excel...tm#debugformat suggested by another. It suggested using CODE() to find out the ASCII. When I used it, it returned 160. Thus you are probably right on the character being the html non-breaking space. Another suggestion to press F2 and then ENTER does not seem to change the nature of the special character. At this point, the issue has become an intellectual challenge. My immediate problem was solved by my manually editing the 45 individual numbers. However, exploring the problem has sensitized me to potential issues. HOWEVER, ANY OTHER IDEAS SHORT OF UPGRADING TO A NEWER VERSION OF XL WILL BE EXPLORED??? Ron Coderre wrote: Perhaps the "spaces" are really html non-breaking spaces. Try something like this: From the Excel main menu: <edit<replace Find what: Hold down the [Alt] key....type 0160...release the [Alt] key Replace with: (leave this blank) Click the [Replace All] button Does that help? *********** Regards, Ron XL2002, WinXP "windsurferLA" wrote: I'm seeking to analyze data copied from stock broker on-line statement. The values copy across as text with (what appears to be) a space before and after each number. I can manually delete the spaces by pressing F2 and the delete or backspace key as appropriate for the space at the front and rear of the number which is otherwise of the form -$123,456.00 The function Value() does not retrieve the value from the text stream. The function Trim() does not remove the spaces. The menu function, "replace" does not find the spaces when you use the space bar to enter the item to be searched for. Is there a special character that appears as a space but is not a space? |
spaces not recognized as spaces
WE GOT A HIT!!!! I learned yet another thing... yes using number pad
allows data entry into FIND of ASCII Char() . with number pad, entering 160 produces character that looks like an "a" which my DOS book says is char(160) . Seems logical. At first, when I entered 0160 into replace, I thought it didn't work. then I noted that the was a blank space in the find field of the find and replace dialog box. I then hit "replace all" and immediate all the mysterious spaces were gone. THANKS Peo Sjoblom wrote: Most likely you have invisible html characters, try this do editreplace, in the find what box hold down alt key and type 0160 on the numpad, release the alt button, leave replace with empty click OK If you do this often you might want to use a macro http://www.mvps.org/dmcritchie/excel/join.htm#trimall same web site has info on how to install macros |
spaces not recognized as spaces
The spaces should be spaces and be recognised as such. However you might find that the problem is what Excel is using to understand where one number starts and another ends. In most import formats, this is handled by a tab character (small right pointing arrow only visible in MS Word when you enable the Printers symbol). Q: Is the stockbroker's detail imported as an ASCII file or are you trying to copy a spreadsheet directly into Excel? I would suggest you try an interim step (I have a similar problem and still haven't found the seamless answer yet): Copy the Stockbrocker statement and paste it into WORD first. Your should end up with something like this: -$154,000.45 $145.541$15,098.98 etc (the is actually a right facing arrow which I can't reproduce here). Note that the second column contains a blank space which Wrod understands as a space or null. If you then copy with from Word into Excel, I think you will end up with numbers and blanks in the right places... Cheers Jon -- Jon Quixley ------------------------------------------------------------------------ Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803 View this thread: http://www.excelforum.com/showthread...hreadid=565238 |
| All times are GMT +1. The time now is 01:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com