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

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.

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?

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))

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...

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

Please Login or Register  to view this content.

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?

Yup:

Please Login or Register  to view this content.

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?

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....

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....