MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Documentation for Clean and Trim Functions


Posted by Anita Hayes on August 28, 2001 8:16 PM

I have large Excel files that contain many columns that
have to be CLEANED and TRIMMED. I have Excel 2000 and
the Help Assistance talks about the functions but does
not tell you how to do it. I need to know how to apply
these 2 functions to an entire column of data' not cell
by cell. I would GREATLY appreciate your contribution
to this problem. Note: many who will need to perform
these functions would be at a Novice level in Excel;
by no means are they Excel experts. Thank you so very
much; I will keep an eye out for your response !!
Anita Hayes


Posted by Teofilo on August 29, 2001 12:59 AM


If you want to Clean and Trim column A, enter the following in B1 (say) and fill down :-
=CLEAN(TRIM(A1))

Posted by Anita Hayes on September 01, 2001 10:27 AM


That worked great; but now I need the values that is in Column B to now be in Column A. I had inserted a column next to A to contain the clean and trim cell data: I cannot delete Column A because it leaves a #ref in column B that I inserted. I also tried copy and paste into Column A and that also doesn't work. Can you please give me the next steps to make this happen ? Thanks !

Posted by Manoel Francisco Dos Santos on September 02, 2001 7:12 AM

Select column B, Copy, PasteSpecial>Values, delete column A.