+ Reply to Thread
Results 1 to 13 of 13

spaces not recognized as spaces

  1. #1
    windsurferLA
    Guest

    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?

  2. #2
    Ron Coderre
    Guest

    RE: 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?
    >


  3. #3
    Peo Sjoblom
    Guest

    Re: spaces not recognized as spaces

    Most likely you have invisible html characters, try this
    do edit>replace, 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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?




  4. #4
    windsurferLA
    Guest

    Re: spaces not recognized as spaces

    Thanks for super fast response by approach didn’t work. I forgot to say
    I’m running XL97.

    The replace or find would not accept any keyboard entries while the
    alt-key way displayed. The machine beeps. I can’t 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 I’d 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?
    >>


  5. #5
    Gord Dibben
    Guest

    Re: 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 <[email protected]> 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?
    >>>



  6. #6
    windsurferLA
    Guest

    Re: spaces not recognized as spaces

    Thanks for super fast response by approach didn’t work. I forgot to say
    I’m running XL97.

    The replace or find would not accept any keyboard entries while the
    alt-key way displayed. The machine beeps. I can’t 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?
    >>


  7. #7
    windsurferLA
    Guest

    Re: 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 <[email protected]> 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?
    >>>>

    >


  8. #8
    windsurferLA
    Guest

    Re: 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 didn’t work. I forgot to say
    > I’m running XL97.
    >
    > The replace or find would not accept any keyboard entries while the
    > alt-key way displayed. The machine beeps. I can’t 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?
    >>>


  9. #9
    windsurferLA
    Guest

    Re: 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 edit>replace, 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
    >
    >
    >


  10. #10
    windsurferLA
    Guest

    Re: spaces not recognized as spaces

    Thanks for super fast response by approach didn’t work. I forgot to say
    I’m running XL97.

    The replace or find would not accept any keyboard entries while the
    alt-key way displayed. The machine beeps. I can’t 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?
    >>


  11. #11
    windsurferLA
    Guest

    Re: 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 <[email protected]> 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?
    >>>>

    >


  12. #12
    windsurferLA
    Guest

    Re: 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 didn’t work. I forgot to say
    > I’m running XL97.
    >
    > The replace or find would not accept any keyboard entries while the
    > alt-key way displayed. The machine beeps. I can’t 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?
    >>>


  13. #13
    windsurferLA
    Guest

    Re: 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 edit>replace, 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
    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1