How to remove spaces in a column in Excel 2010?
In this column with city names, some of the cities start with a space.
This becomes very apparent if you sort on this column. As you can see Excel first sorts the city names that start with a space and then the rest. This is not the way I want the data so I want to remove these spaces. I’ll insert a temporary empty column by right-clicking and selecting “Insert”, and then I’ll go back to the “Formulas” tab and click “Insert function”, again I’ll write a short description “Clear spaces.” Here Excel suggests a function called “TRIM” which removes unnecessary spaces.
I’ll mark the first cell in the “City” column and click “OK”. Again I’ll copy the formula by double-clicking in the bottom right corner. I’ll right-click and drag the content over, and select “Copy Here as Values Only”. Then I’ll remove the temporary column by right-clicking and selecting “Delete”. Now I want to sort this column again, so I’ll go to the “Data” tab and click sort “A-Z” and now the city names are sorted properly.