C1 has a "Text" format and it contains: 123456

When I insert a new column (D), the format of its cells is (by default)
"Text".

In D1, I enter the formula: =VALUE(c1/100)

Because the format of D1 is "Text", D1 contains the literal
"=VALUE(c1/100)"

I then do FORMAT/CELLS, click GENERAL then click "OK".

When I press f2 and press "Enter", D1 now contains the value: 1234.56

However, if I later change the formula of D1, the "General" format
changes back to "Text" and it again contains the literal
"=VALUE(c1/100)"

How can I prevent the format from changing?

I don't think you can change the way excel likes to help.

But maybe you could just drop the Text formatting (keep the cell formatted as
General), but do your data entry as '123456 (include that leading apostrophe).



wrote:

C1 has a "Text" format and it contains: 123456

When I insert a new column (D), the format of its cells is (by default)
"Text".

In D1, I enter the formula: =VALUE(c1/100)

Because the format of D1 is "Text", D1 contains the literal
"=VALUE(c1/100)"

I then do FORMAT/CELLS, click GENERAL then click "OK".

When I press f2 and press "Enter", D1 now contains the value: 1234.56

However, if I later change the formula of D1, the "General" format
changes back to "Text" and it again contains the literal
"=VALUE(c1/100)"

How can I prevent the format from changing?

--

Dave Peterson

Hi Dave,

I just noticed that the cells have a "text" format when their column is
inserted NEXT TO a column with "text"-formatted cells. However, the
cells have a "general" format when their column is inserted a few
columns AWAY FROM a column with "text"-formatted cells.

gary

Yep. Excel likes to help. It's sometimes (usually???) irritating.

I would import an ASCII file and want to make sure a column was treated as
text. Then insert that adjacent column and have to remember to change the
format back to General.




wrote:

Hi Dave,

I just noticed that the cells have a "text" format when their column is
inserted NEXT TO a column with "text"-formatted cells. However, the
cells have a "general" format when their column is inserted a few
columns AWAY FROM a column with "text"-formatted cells.

gary

--

Dave Peterson