How do I remove special “ ” spaces in excel column

问题:

I am using Office 2003 under windows XP Pro

I copy and paste large lists from ms access 2003 database into excel daily so I can do various functions to the lists. The one problem I am having now is figuring out how to remove the spacial spaces (I thought it was called white space but it seems not the case) after the numbers or text.

example:

[1234     ]
[1234     ]
[1234     ]
[1234     ]
[34     ]
[12ABC     ]

(please note, the example above will work if you copy/paste. I was just giving example, visually.)

In excel, =trim() does not remove them. Also, (Search/Replace " " and leave empty) does not remove them. I have also tried a trick I use in other situation, copy/paste into notepad and then search/replace, did not remove them. That is why I called the term, special. But there is probably an actual term--I wouldn't mind knowing what its called, officially.

I am not looking for a custom =function() in excel to run.

This would not be practical to use in every day copy/paste since these are going directly into someone elses excel reports. I am adding my info to it and it gets processed further by others who are not excel savvy.

回答1:

Have you tried the =clean() function? It removes many unusual characters.

Also you could use the =substitute() function if you know which specific ascii characters are being naughty. They likely are #160's as suggested in the comments.

=SUBSTITUTE(A1,char(160),"")

would work for that instance.

Hope that helps