+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 18

Non-Breaking Space

  1. #1
    Registered User
    Join Date
    10-06-2011
    Location
    New York, New York
    MS-Off Ver
    Excel 2003
    Posts
    30

    Non-Breaking Space

    Hello,

    Recently I created a macro that runs pretty smoothly. However, I've encountered a problem where when we paste information the spaces are sometimes pastes as (hex 20) and sometimes it is pasted as (hex A0).

    This has created a lot of problems because I basically paste info sent by clients into the spreadsheet and the extract information using a platform called Bloomberg Terminal Add ins.

    Does anyone know of a way that I can ensure that spaces gets written and pasted using only the (hex20) regular space character?

    As always thank you for your insights!!!!
    Last edited by cocolete; 10-18-2011 at 03:43 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,031

    Re: Non-Breaking Space

    Might help if you posted your code.

    Regards
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,635

    Re: Non-Breaking Space

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    10-06-2011
    Location
    New York, New York
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Non-Breaking Space

    TMSchuks,

    I apologize for not posting a code. In all honesty didn't have one. This concept was brought to my attention today and have been studying more on the subject.

    I know it is very vague question I just wanted to know if codes can be written....which from your reply I now think they can....and just basically go in the right direction from there.

    THANK YOU FOR YOUR PROMPT REPLY

  5. #5
    Registered User
    Join Date
    10-06-2011
    Location
    New York, New York
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Non-Breaking Space

    shg,

    Is this code something that I would write in VBA and would be implemented in the whole workbook. Or do I have to write it in a module and incorporate it within a Macro?

    THANK YOU

  6. #6
    Registered User
    Join Date
    10-06-2011
    Location
    New York, New York
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Non-Breaking Space

    PS:

    The macro is very simple it is just a recorded macro where I copy and paste the info on sheet one and it will be "pasted" and formatted into another sheet.

    I receive the info from lets say an "email" and my sheet just cleans the info up.

    Module 5 and Module 7 run the codes for the submit unit or denomination trade

    Module 5 is composed of module 6 (SubImpl3) and Module 8 (SubImpl5)

    Module7: is composed of Module2 (SubImpl2) and Module 9 (sUBiMPL6)
    Attached Files Attached Files

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,635

    Re: Non-Breaking Space

    Now that you've posted your code, you're in very good hands with TMS.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,031

    Re: Non-Breaking Space

    @RS: I take it you've looked at the code ?

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,031

    Re: Non-Breaking Space

    This routine will change non-breaking spaces to normal spaces. Call it after your current macro code.

    Please Login or Register  to view this content.

    It goes at the end of the Back2Back module:

    Please Login or Register  to view this content.


    This will make your macro execute more smoothly; it switches ScreenUpdating off and on so you don't see all the screen swaps.

    Please Login or Register  to view this content.

    Regards

  10. #10
    Registered User
    Join Date
    10-06-2011
    Location
    New York, New York
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Non-Breaking Space

    First of all guys thanks for your replies.

    TMS...I already included the Application.ScreenUpdating into the sheets and WOW runs a lot more smoothly now...THANKS

    The VBA code I'm sure it works but using a formula (StringToHex) I saw that the string of the "space" value that is causing problems is A0. I thought that it was a question of just changing the "What:=Chr(160)" with "What:=Chr(A0)" but that didn't work.
    Attached Files Attached Files

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,031

    Re: Non-Breaking Space

    And your problem with A0 is .... ?

    =CODE(MID(A2,10,1)) = 160

    =StringToHex(MID(A2,10,1)) = A0

    So, you're sure the VBA code works BUT ... ?


    I think the point is to look at the character string before the code is run and the character string after it has run and see if you have normal spaces.

    Regards

  12. #12
    Registered User
    Join Date
    10-06-2011
    Location
    New York, New York
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Non-Breaking Space

    hahaha sorry I swore it where 2 dif texts.

    For example when I write that it doesn't eliminate the space:

    48123YPV2*cc

  13. #13
    Registered User
    Join Date
    10-06-2011
    Location
    New York, New York
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Non-Breaking Space

    that * was actually the " " = A0 but pasted like that

  14. #14
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,031

    Re: Non-Breaking Space

    Sorry, does the code do what you want or not? I'm not sure if you're askig a question.

    Regards

  15. #15
    Registered User
    Join Date
    10-06-2011
    Location
    New York, New York
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Non-Breaking Space

    No it is not.

    Sorry for the confusion

+ Reply to Thread
Page 1 of 2 1 2 LastLast

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