applying trim on a column in excel

XML, Perl, Python, and other languages can be discussed here, even if it isn't PHP (We might forgive you).

Moderator: General Moderators

Post Reply
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

applying trim on a column in excel

Post 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.
User avatar
dude81
Forum Regular
Posts: 509
Joined: Mon Aug 29, 2005 6:26 am
Location: Pearls City

Post 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.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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?
User avatar
dude81
Forum Regular
Posts: 509
Joined: Mon Aug 29, 2005 6:26 am
Location: Pearls City

Post 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.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
User avatar
dude81
Forum Regular
Posts: 509
Joined: Mon Aug 29, 2005 6:26 am
Location: Pearls City

Post 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.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
User avatar
dude81
Forum Regular
Posts: 509
Joined: Mon Aug 29, 2005 6:26 am
Location: Pearls City

Post 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.
Post Reply