删除整个数据列中的前导或尾随空格

How do I remove leading or trailing spaces of all cells in an entire column?

如何删除整个列中所有单元格的前导空格或尾随空格?

The worksheet's conventional Find and Replace (aka Ctrl+H) dialog is not solving the problem.

工作表的常规查找和替换(即Ctrl+H)对话框不能解决问题。

6 个解决方案

#1

125

If you would like to use a formula, the TRIM function will do exactly what you're looking for:

如果你想使用一个公式，修剪功能将会做你想要的:

+----+------------+---------------------+
|    |     A      |           B         |
+----+------------+---------------------+
| 1  | =TRIM(B1)  |  value to trim here |
+----+------------+---------------------+

So to do the whole column...
1) Insert a column
2) Insert TRIM function pointed at cell you are trying to correct.
3) Copy formula down the page
4) Copy inserted column
5) Paste as "Values"

所以要做整列。1)插入一列2)插入修剪功能指向你要纠正的单元格。3)向下复制公式4)复制插入列5)粘贴为“值”

Should be good to go from there...

从那里去应该很好……

#2

218

Quite often the issue is a non-breaking space - CHAR(160) - especially from Web text sources -that CLEAN can't remove, so I would go a step further than this and try a formula like this which replaces any non-breaking spaces with a standard one

通常情况下，问题是一个不间断的空间——CHAR(160)——尤其是来自Web文本源的——不能删除，因此我将更进一步，尝试使用一个标准的格式来替换任何不间断的空间

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

=修剪(清洁(替代(A1,CHAR(160)," ")))

Ron de Bruin has an excellent post on tips for cleaning data here

Ron de Bruin在这里有一篇关于清洁数据技巧的优秀文章

You can also remove the CHAR(160) directly without a workaround formula by

您还可以直接删除CHAR(160)，而无需使用变通公式by

Edit .... Replace your selected data,
编辑....取代你选定的数据,
in Find What hold ALT and type 0160 using the numeric keypad
在找到什么按住ALT和输入0160使用数字键盘
Leave Replace With as blank and select Replace All
以空白替换，并选择全部替换。

#3

If it's the same number of characters at the beginning of the cell each time, you can use the text to columns command and select the fixed width option to chop the cell data into two columns. Then just delete the unwanted stuff in the first column.

如果每次单元格开头的字符数相同，可以使用“文本到列”命令并选择“固定宽度”选项将单元格数据分割为两列。然后删除第一列中不需要的内容。

#4

Without using a formula you can do this with 'Text to columns'.

不使用公式，你可以使用“文本到列”。

Select the column that has the trailing spaces in the cells.
选择单元格中具有尾随空格的列。
Click 'Text to columns'. Choose option 'Fixed width'.
点击“文本列”。选择选项“固定宽度”。
Set a break line so the longest text will fit. If your largest cell has 100 characters you can set the breakline on 200 or whatever you want.
设置一个断行，这样最长的文本才适合。如果您最大的单元格有100个字符，您可以将断点设置为200或任何您想要的。
Finish the operation.
完成这项操作。
You can now delete the new column Excel has created.
现在可以删除Excel创建的新列。

The 'side-effect' is that Excel has removed all trailing spaces in the original column.

“副作用”是Excel删除了原始列中的所有尾随空格。

#5

I've found that the best (and easiest) way to delete leading, trailing (and excessive) spaces in Excel is to use a third-party plugin. I've been using ASAP Utilities for Excel and it accomplishes the task as well as adds many other much-needed features. This approach doesn't require writing formulas and can remove spaces on any selection spanning multiple columns and/or rows. I also use this to sanitize and remove the uninvited non-breaking space that often finds its way into Excel data when copying-and-pasting from other Microsoft products.

我发现最好(也是最简单的)删除Excel中领先、尾随(和过度)空格的方法是使用第三方插件。我一直在使用ASAP实用程序来实现Excel，它完成了任务，并添加了许多其他需要的特性。这种方法不需要编写公式，并且可以删除跨越多个列和/或行的任何选择上的空格。我还使用它来清理和删除未被邀请的非中断空间，当从其他Microsoft产品复制粘贴时，这些空间通常会进入Excel数据。

More information regarding ASAP Utilities and trimming can be found here:

更多关于ASAP实用程序和修整的信息可以在这里找到:

http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=87

#6

-2

I was able to use Find & Replace with the "Find what:" input field set to:

我可以使用Find和Replace用“Find what:”输入字段设置为:

" * "

“*”

(space asterisk space with no double-quotes)

(无双引号的星号空间)

and "Replace with:" set to:

和“替换”:设置为:

""

”“

(nothing)

(没有什么)

#1

125