133

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.

ChrisM
  • 1,576
  • 6
  • 18
  • 29
venkat
  • 5,648
  • 16
  • 58
  • 83
  • Could you clarify "Without code" ? Do you mean without vba just with formula's? – hydrox467 Mar 06 '12 at 05:38
  • I don't know if the ribbonified Excel 2010 can still export to comma or tab separated value format, but if so, export it in one of those formats, modify in notepad (or whatever) and import it back. – technosaurus Mar 06 '12 at 05:48
  • 1
    without 'VBA' is great!! – venkat Mar 06 '12 at 05:50
  • 6
    Not sure why it was closed as off topic as its a legit question and Excel can be considered a functional programming language - his question was how to achieve the desired result given the set of Excel functions. – J.D. Apr 18 '15 at 18:24
  • A useful link is this one http://www.extendoffice.com/documents/excel/667-excel-remove-first-space.html – Pietro Biroli Mar 10 '16 at 08:40
  • 1
    Be careful with these answers if you want to keep interior spaces between words. TRIM will remove ALL extra spaces, not just leading and trailing. – Chiramisu Oct 17 '17 at 19:16

6 Answers6

234

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

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

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

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

  • Edit .... Replace your selected data,
  • in Find What hold ALT and type 0160 using the numeric keypad
  • Leave Replace With as blank and select Replace All
brettdj
  • 54,857
  • 16
  • 114
  • 177
138

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

brettdj
  • 54,857
  • 16
  • 114
  • 177
hydrox467
  • 1,705
  • 1
  • 12
  • 15
  • 7
    This `Trim` Doesn't worked out. still the resultant is getting with spaces – venkat Mar 06 '12 at 05:51
  • can you post an example of what didn't work out? Like a cell's worth of data that still has spaces after running the trim? – hydrox467 Mar 06 '12 at 05:54
  • 21
    My guess is there are other characters then "spaces" in what you're trying to remove. You could try =TRIM(CLEAN(B1)) which will remove all non-printable characters and any leading/trailing spaces. – hydrox467 Mar 06 '12 at 06:00
  • 4
    Note that this solution will also delete consecutive embedded spaces - which might NOT be what you want, if you were hoping to just remove leading and trailing spaces! – Loophole Nov 11 '13 at 05:24
  • 4
    trim will delete extra spaces in between words other than beginning and trailing spaces – kaushik Jan 18 '15 at 14:12
  • 2
    I would not expect this, and there may be something I'm doing wrong - TRIM appears to be eliminating duplicated spaces in the middle of my string. Is there a way to only trim off leading and trailing spaces? – Shavais Oct 16 '15 at 15:17
  • thanks. Trim worked eliminating the spaces at the end of my strings – Vasile Surdu Feb 07 '17 at 09:50
18

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.

coip
  • 1,312
  • 16
  • 30
Joris
  • 181
  • 1
  • 2
7

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.

Katherine Chalmers
  • 414
  • 5
  • 14
1

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

James Moberg
  • 4,360
  • 1
  • 22
  • 21
-2

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)

Cody Gray - on strike
  • 239,200
  • 50
  • 490
  • 574
Mr Roman
  • 17
  • 1
  • 5