+ Reply to Thread
Results 1 to 5 of 5

Remove leading, trailing & non-breaking spaces on a worksheet

  1. #1
    Registered User
    Join Date
    11-01-2006
    Posts
    16

    Remove leading, trailing & non-breaking spaces on a worksheet

    Hi

    I'm an Excel 2010 user and I regularly receive worksheet data that contains leading, trailing & non-breaking spaces.

    I remove them by using the worksheet functions =TRIM(CLEAN(SUBSTITUTE(B1,CHAR(160)," ")))

    The field that normally contains the troublesome spaces doesn't always appear in column B and because I get them regulary I was hoping someone could enlighten as to how I could create this as a macro?

    Many thanks in advance.

    Kenneth

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Remove leading, trailing & non-breaking spaces on a worksheet

    Try,

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    11-01-2006
    Posts
    16

    Re: Remove leading, trailing & non-breaking spaces on a worksheet

    Hi AB33

    Many thanks, after a few tests, that looks as if it worked a treat!
    In the interest of learning can you give a brief explanation of how it works - i understand char 160 but not much else?

    Once again, many thanks.

    Kenneth

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Remove leading, trailing & non-breaking spaces on a worksheet

    Looping through used range and replacing each cell which has chr 160 with nil string.
    You do not have to worry about which column the code refers to. It goes through each used range, but there is a price to pay-Speed will be slow as the loop has to go though the used range.

    There may be a short cut and faster code which probably does not require looping.

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

    Re: Remove leading, trailing & non-breaking spaces on a worksheet

    Maybe

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. insert leading or trailing spaces
    By 2008 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-05-2013, 08:58 PM
  2. [SOLVED] Removing leading and trailing spaces
    By mangesh in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-18-2013, 05:25 AM
  3. delete leading trailing and excessive spaces
    By djwaz69 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-28-2012, 03:59 AM
  4. [SOLVED] Strip Leading and Trailing Spaces
    By Vladamir in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-19-2012, 03:46 PM
  5. How keep Leading/Trailing spaces when save in Excel?
    By jorgejulio in forum Excel General
    Replies: 0
    Last Post: 08-01-2006, 04:49 PM

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