Formula entries get turned into textual entries

[herrdue]

I received a workbook from another company and wanted to add a new
column of formulas. However, even though I included the = sign and
ensured the correct formula, my entry into the cell would suddenly
convert it to text, showing a ' or ^ character in front of the = sign.
I discovered if I clear all formatting and data entry from the cells,
it would then allow me to enter formulas. I've also found that if I
create new workbooks/spreadsheets, this does not happen. I experience
the same odd behavior in 2003 and 2007. This suggests there's
something wrong with the file the other company sent to me...but I
would like to know what they may have done to the spreadsheet to cause
this anomally to happen. Any ideas?

 

[Gord Dibben]

1. The workbook has cells pre-formatted as text

Re-format all to General then re-enter the formulas.

2. The ' or ^ preceding the text means the workbook has Lotus Transition
Options checked.

' means left-aligned...................^ means center-aligned...........""
would be right-aligned

Uncheck those.........in 2003 it would be Tools>Options>Transition.


Gord Dibben MS Excel MVP

 

[ShaneDevenshire]

Hi,

And where is the Lotus Transition option?
2003 - choose Tools, Options, Transition, and uncheck all checkboxes.
2007 - choose Office Button, Excel Options, Advanced tab, last two sections
on the page, particularly the Transition formula evaluation and Transition
formula entry should be unchecked.

However, I believe that when you are in the Transition mode and you enter a
formula into a cell, even if the cells are pre-formatted to text, they will
not display ^ or ' or ". The formulas will be displayed as =SUM(A1:A10) as
text, but without the leading character.

I don't have 1-2-3 installed but I suspect that the files were created in
1-2-3.

 

[Gord Dibben]

Correct.

However, the formula bar will show the ', ^ or "

I misread..........did not see the part where OP saw these in the
text-formatted cell.


Gord