Trim Again

[Guest]

I was going absolutely great with =Trim (see below Kassie & Neil's
assistance) and all of a sudden I found that I was not getting a list of
corrected data and that the formula 'remained' in the target cell and did not
display the new trimmed numbers or text. (I am having to create a new
column by the way to put in the new data.)
So the sequence of events is:
The data I need to trim - which beings at L2 is in column L
I create new column next to it (which becomes column M)
Click on cell M2
Type formula =TRIM(L2) then enter
BUT the data in cell M2 remains a formula and does not become the trimmed
number and also f I drag the formula down to fill the rest of the column in
M, the formula doesnt increase sequential (ie in comun M it should be
=TRIM(L2) then =TRIM(L3) etc etc but remains as =TRIM(L2)
Normally addition there is a blue box round the cell that I am Trimming as I
am entering the formula in the target cell and there is a yellow line on the
left hand side of the target cell. BUT when =TRIM doesnt work, I get a yellow
box round the target cell as I type the formula.
I have looked for any signs of protection and have also making sure that
cells formated as text and have toggled Control +` . View formulas is not
ticked in Options.
Help again please - so sorry for being dim.

 

[Guest]

Hi
Have a look to make sure that Calculation is set to Automatic. It's on
Tools|Options|Calculation tab.

 

[Guest]

Hi Andy - thanks for the response but it is set to Automatic.

 

[David McRitchie]

Also make sure that you did not format your cells with the
formulas as Text. You want them as General so that the
formulas will work.

That said, for what you are doing a macro would make
this much simpler as you can change your text data
where it resides, rather than having to introduce formulas
and a messy cleanup afterwards to remove those formulas.
See the TrimALL macro in
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[Guest]

You say you've checked that the cell is not formatted as text. If the cell
is formatted as text before you type the formula, changing the format
afterwards will not make any difference. Try formatting the text as General
and then retyping the formula, or hit F2 while the cell is selected and then
hit Enter to complete.

 

[Guest]

Andy and David - thank you, thank you! I shall have to work a bit at getting
my head round the macro, but for the time being I am back in business.
Thanks for your patience. ferdy
-----------------------------------------------------