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

User · 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

User · Answer

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 87

User · Answer

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

User · Answer

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

User · Answer

I was able to use Find  amp  Replace with the  Find what   input field set to               space asterisk space with no double-quotes   and  Replace with   set to            nothing

User · Answer

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

[excel] Remove leading or trailing spaces in an entire column of data

Examples related to excel

Examples related to excel-formula

Examples related to excel-2010