如何删除特殊的“ " excel列中的空格？ [英] How do I remove special " " spaces in excel columns?

问题描述

我正在Windows XP Pro下使用Office 2003

我将ms access 2003数据库中的大型列表复制并粘贴到excel中，以便我可以对列表执行各种功能。现在我遇到的一个问题就是弄清楚如何去除空间空间（我认为它被称为空白空间，但似乎不是这样）。

 示例：
 
 [1234] 
 [1234] 
 [1234 ] 
 [1234] 
 [34] 
 [12ABC] 
  

（请注意，如果你复制/粘贴，上面的例子将会起作用，我只是给出了一个例子。）

在excel中， = trim（）不会删除它们。此外，（搜索/替换并留空）不会删除它们。我也尝试过在其他情况下使用的技巧，复制/粘贴到记事本中，然后搜索/替换，没有删除它们。这就是为什么我叫这个词，特别的。但是可能有一个实际的术语 - 我不介意知道它正在被称为什么。

我不是在寻找一个custom = function（）在excel中运行。

这不是实用的每一天复制/粘贴，因为这些直接进入某人elses excel报告。我添加了我的信息，并且被其他不精明的人进一步处理。

解决方案

你尝试过= clean（）函数？它可以删除许多不寻常的字符。

如果您知道哪些具体的ascii字符是顽皮的，您还可以使用= substitute（）函数。

  = SUBSTITUTE（A1，char（160），）
  

将适用于该实例。

希望帮助

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.

解决方案

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

这篇关于如何删除特殊的“ " excel列中的空格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！