I have a column in an excel document that each entry has a trailing space. When I copy/paste the value into another form I have to manually remove the trailing space. Is there an easy way to batch remove all the trailing spaces through an entire column?
Its a 2 step process:
Use the TRIM function
=TRIM(Cell)
To remove the spaces. Basically make a parallel column that is TRIMmed. Copy the trimmed cells, then PASTE SPECIAL to paste the VALUES back to your original cells.
Clarified step by step:
Make a new column with
=TRIM(old column)in the cellsSelect that whole column (the new one with the formula)
Copy that whole column
Select the whole OLD column with the trailing spaces
Right click it
Select "Paste Special"
Hit the radio button for "Values"
Hit "OK"
-
So I select the column that has "=TRIM(CELL)" in each cell, copy it, then paste special on to the column that I need to be trimmed? That does not work for me. – GavinR Jul 29 '10 at 21:16
-
Hmm, it should. You may need to paste once more into a 3rd column to get a column with just the values to paste back into the target column. – JNK Jul 29 '10 at 23:14
-
If the content is numeric values with trailing spaces you can select the column, edit>copy and into another column paste special>values>add and skip doing the trim. If it is text, do as mentioned previously by JNK
