Page 1 of 1

applying trim on a column in excel

Posted: Tue Jan 30, 2007 4:28 am
by raghavan20
let us say there is an excel workbook having a few columns. one column has values text but they have to be trimmed i.e. all rows under that column has to be trimmed even when we put values in there which may spaces in them.

how to apply a function for a column? i know how to apply for a cell but i could not find the way for the entire column.

any help is appreciated.

Posted: Tue Jan 30, 2007 4:34 am
by dude81
Apply formula for one cell, the rest drag and drop till the last cell in the same column. The formula will be applicable for all.

Posted: Tue Jan 30, 2007 4:41 am
by raghavan20
thanks for your reply.

I am trying to apply trim for a cell.
lets say cell is a2 and i put =trim(a2) then it says that i would create a circular reference. how to solve this?

Posted: Tue Jan 30, 2007 4:50 am
by dude81
Circular reference??
I understand your question as follows, You have A2-AN which needs to be applied trim function.Then

Code: Select all

=trim(A2);
and press enter.
Hold the mouse over the cell and drag and drop till 'AN', this should apply the formula for the entire column.

I hope Im right in my understanding.

Posted: Tue Jan 30, 2007 4:57 am
by raghavan20
dude81 wrote:Circular reference??
I understand your question as follows, You have A2-AN which needs to be applied trim function.Then

Code: Select all

=trim(A2);
and press enter.
Hold the mouse over the cell and drag and drop till 'AN', this should apply the formula for the entire column.

I hope Im right in my understanding.
you have understood things correctly but the solution does not work. have you tried it in an excel sheet?
it says 'the formula you typed contains an error' when i put for the cell a2 the formula =trim(a2). thanks.

Posted: Tue Jan 30, 2007 5:13 am
by dude81
Yes, I understood your problem, Put the formula in some new column and copy and paste the result back over the original coloumn.

Posted: Tue Jan 30, 2007 5:31 am
by raghavan20
dude81 wrote:Yes, I understood your problem, Put the formula in some new column and copy and paste the result back over the original coloumn.
alright, looks like this problem cannot be solved other than the solution you have said now.
it is bad to know that there is no way to enforce a trim on a column.

Posted: Tue Jan 30, 2007 6:12 am
by dude81
You mean to say enforce, meaning by default the formula is applicable whenever you enter the data,

we can format a column by default (this happens by selecting, and right click, format, custom(probably)), but by formula is a doubt.
I think write a macro, that should be great.