+ Reply to Thread
Results 1 to 12 of 12

Clean and Trim Not Working

  1. #1
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Clean and Trim Not Working

    I have a series of numbers which have spaces at the end, which I need to remove. I started by running a 'Trim' macro over the numbers but this didn't remove the spaces, so I assumed there must be special characters after the numbers, so I then tried running a 'Clean' macro over the numbers, followed by the 'Trim' Macro, but this still doesn't remove the extra spaces after the numbers.

    Can anyone identify what the problem is? Both Macros are included in the attached workbook.

    Is it simply the case that the 'Clean' macro isn't cleaning the string, if so, how can I adapt this to remove any special characters? Also, how do I tell there are special characters there in the first place?

    Is there an easy way to then combine the two macros so that it first cleans and then trims?

    Many thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Clean and Trim Not Working

    All of the numbers have character 160 appended to the end of them. This will sort it out:

    Please Login or Register  to view this content.

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Clean and Trim Not Working

    the space is char 160
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Clean and Trim Not Working

    That did the trick, many thanks...

    Can I ask how you were able to tell that the numbers had the space char 160 after them, I assume this is a special character and not a space which is why 'Trim' had no effect?

  5. #5
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Clean and Trim Not Working

    It is indeed not a space - space has the ASCII code 32. Character 160 is some other character that shows up as nothing in Excel.

    I used a formula to find out what it was:

    =CODE(RIGHT(A1,1))

  6. #6
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Clean and Trim Not Working

    Okay, thanks for that. I've now reaslised the problem is slightly more complex. If I remove the additional space manually and then run the macro, it removes the last character from my number?

    Is there any way to adapt the macro so that it only removes special characters, regardless of what they are, without removing any of the actual numberical (visible) charachters. It is quite possible that the actual data set (some 20,000 rows) has a complete mixture of extraneous characters as the data comes from multiple sources.

    Some numbers don't have any special characters after them, some do...

  7. #7
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Clean and Trim Not Working

    Something like this - it will clean out all of the non-numeric characters in a selection:

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Clean and Trim Not Working

    So, that works, however if the cell is already blank, it inserts a '0' in the cell. Is there a way to prevent that from happening?

  9. #9
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Clean and Trim Not Working

    Yup:

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Clean and Trim Not Working

    Thanks Andrew, that fixed the problem...

    My only confusion now is that after running your macro, if I then run =CODE(RIGHT(A1,1)) over the 'cleaned' cells, I get a range of numbers between 48 and 57, which I assume are the charachters 0 to 9, a) why the variation and b) why are they there at all? The order seems quite random, e.g. where char 160 appeared originally that could now be replaced with any character, if that makes sense?

  11. #11
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Clean and Trim Not Working

    Okay, just figured out it is displaying the last character of the actual number, so I can sleep easy tonight...

    Many thanks for all your help, it is greatly appreciated....

  12. #12
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Clean and Trim Not Working

    Okay, just figured out it is displaying the last character of the actual number, so I can sleep easy tonight...

    Many thanks for all your help, it is greatly appreciated....

+ 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