Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Trim Again
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. |
#2
|
|||
|
|||
Hi
Have a look to make sure that Calculation is set to Automatic. It's on Tools|Options|Calculation tab. -- Andy. "ferdy" wrote in message ... 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. |
#3
|
|||
|
|||
Hi Andy - thanks for the response but it is set to Automatic.
"Andy B" wrote: Hi Have a look to make sure that Calculation is set to Automatic. It's on Tools|Options|Calculation tab. -- Andy. "ferdy" wrote in message ... 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. |
#4
|
|||
|
|||
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 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm <Andy B wrote in message ... Hi Have a look to make sure that Calculation is set to Automatic. It's on Tools|Options|Calculation tab. -- Andy. "ferdy" wrote in message ... 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. |
#5
|
|||
|
|||
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. -- Andy. "ferdy" wrote in message ... 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. |
#6
|
|||
|
|||
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 ----------------------------------------------------- "Andy B" wrote: 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. -- Andy. "ferdy" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trim data in charts | Charts and Charting in Excel | |||
Excel TRIM() can't handle in pasted HTML | Excel Discussion (Misc queries) | |||
Trim function doesn't clean out ASCII Code 160 (Space) | Excel Worksheet Functions | |||
Trim function | Excel Worksheet Functions | |||
How to use TRIM function | Excel Worksheet Functions |