Advertisement
Advertisement


Remove leading or trailing spaces in an entire column of data


Question

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.

2017/08/06
1
128
8/6/2017 7:20:00 AM

Accepted Answer

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"

Should be good to go from there...

2012/03/11
133
3/11/2012 4:45:21 AM


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' from the 'Data' tab, then 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.
  • Finish the operation.
  • You can now delete the new column Excel has created.

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

2018/08/24

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.

2012/03/10

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.

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

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

2017/09/19

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

" * "

(space asterisk space with no double-quotes)

and "Replace with:" set to:

""

(nothing)

2017/08/02

Source: https://stackoverflow.com/questions/9578397
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]