Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 6 down vote accepted

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 cells

  • Select 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"

share|improve this answer
    
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
    
It worked for me. I'll clarify the answer. –  JNK Jul 30 '10 at 0:27
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.