In a column (Excel2007) that is correctly formatted as numeric, one cell has
=INT(15/0.85+0.5) and has correct format as numeric, another cell has
=INT(75/0.85+0.5) and is formatted as text €“ both cells show actual values
(not formula). All the cells(over 1000) in this column have similar content,
and all except TWO have correct numeric format.
I have tried, without success, various ways to change the 2 text cells to
numeric .
I am baffled by this (why these 2 cells are different and why I cannot
change their format).
Any comments/suggestions ?
SM

The usual way is to perform a mathematical operation on the contents of the
cell. Select an out-of-the-way cell and enter a 1 into that cell. With
that cell selected, do Edit - Copy. Now select the cells with the problem.
Do Edit - PasteSpecial - Multiply. That should force a text number to a
number. HTH Otto
"SM" wrote in message
...
In a column (Excel2007) that is correctly formatted as numeric, one cell
has
=INT(15/0.85+0.5) and has correct format as numeric, another cell has
=INT(75/0.85+0.5) and is formatted as text - both cells show actual values
(not formula). All the cells(over 1000) in this column have similar
content,
and all except TWO have correct numeric format.
I have tried, without success, various ways to change the 2 text cells to
numeric .
I am baffled by this (why these 2 cells are different and why I cannot
change their format).
Any comments/suggestions ?
SM

Thanks for your response.
Unfortunately, any mathematical operation gives the resultant value - does
not keep the formula used - I need to have the formula in the cells for later
updates (individually).
For now, I have solved this problem by asking Excel to "ignore the error" -
using the pop-up icon next to the cell.
Still would like to know why this error occurs and why it cannot be solved
by conventional ways.
SM

"Otto Moehrbach" wrote:

The usual way is to perform a mathematical operation on the contents of the
cell. Select an out-of-the-way cell and enter a 1 into that cell. With
that cell selected, do Edit - Copy. Now select the cells with the problem.
Do Edit - PasteSpecial - Multiply. That should force a text number to a
number. HTH Otto
"SM" wrote in message
...
In a column (Excel2007) that is correctly formatted as numeric, one cell
has
=INT(15/0.85+0.5) and has correct format as numeric, another cell has
=INT(75/0.85+0.5) and is formatted as text - both cells show actual values
(not formula). All the cells(over 1000) in this column have similar
content,
and all except TWO have correct numeric format.
I have tried, without success, various ways to change the 2 text cells to
numeric .
I am baffled by this (why these 2 cells are different and why I cannot
change their format).
Any comments/suggestions ?
SM

What is the format of those cells? If a cell is formatted as Text, clear
(erase) the cell. Then format the blank cell like you want. Then insert
the formula into the cell. Placing a number in a cell that is formatted to
Text will not change the format. Format the cell when it is blank. HTH
Otto

"SM" wrote in message
...
Thanks for your response.
Unfortunately, any mathematical operation gives the resultant value - does
not keep the formula used - I need to have the formula in the cells for
later
updates (individually).
For now, I have solved this problem by asking Excel to "ignore the
error" -
using the pop-up icon next to the cell.
Still would like to know why this error occurs and why it cannot be solved
by conventional ways.
SM

"Otto Moehrbach" wrote:

The usual way is to perform a mathematical operation on the contents of
the
cell. Select an out-of-the-way cell and enter a 1 into that cell. With
that cell selected, do Edit - Copy. Now select the cells with the
problem.
Do Edit - PasteSpecial - Multiply. That should force a text number to a
number. HTH Otto
"SM" wrote in message
...
In a column (Excel2007) that is correctly formatted as numeric, one
cell
has
=INT(15/0.85+0.5) and has correct format as numeric, another cell has
=INT(75/0.85+0.5) and is formatted as text - both cells show actual
values
(not formula). All the cells(over 1000) in this column have similar
content,
and all except TWO have correct numeric format.
I have tried, without success, various ways to change the 2 text cells
to
numeric .
I am baffled by this (why these 2 cells are different and why I cannot
change their format).
Any comments/suggestions ?
SM

Thanks for your comment.
Not only had I done your suggestion, I had also tried various combinations:
deleted contents and formatted the whole column as numeric;
deleted contents and formatted the whole row as numeric;
deleted contents of the cell and several surrounding cells and formatted as
numeric;
and several other combinations, which left me very frustrated.
In each case the (offending) cell would become formatted as numeric, but as
soon as I typed in the formula, it would revert back to text format. No other
cell would be affected.
Very strange and very baffling.
I can only guess some very higher setup option (unknown to mere mortals like
me)needs to be adjusted.
SM


"Otto Moehrbach" wrote:

What is the format of those cells? If a cell is formatted as Text, clear
(erase) the cell. Then format the blank cell like you want. Then insert
the formula into the cell. Placing a number in a cell that is formatted to
Text will not change the format. Format the cell when it is blank. HTH
Otto

"SM" wrote in message
...
Thanks for your response.
Unfortunately, any mathematical operation gives the resultant value - does
not keep the formula used - I need to have the formula in the cells for
later
updates (individually).
For now, I have solved this problem by asking Excel to "ignore the
error" -
using the pop-up icon next to the cell.
Still would like to know why this error occurs and why it cannot be solved
by conventional ways.
SM

"Otto Moehrbach" wrote:

The usual way is to perform a mathematical operation on the contents of
the
cell. Select an out-of-the-way cell and enter a 1 into that cell. With
that cell selected, do Edit - Copy. Now select the cells with the
problem.
Do Edit - PasteSpecial - Multiply. That should force a text number to a
number. HTH Otto
"SM" wrote in message
...
In a column (Excel2007) that is correctly formatted as numeric, one
cell
has
=INT(15/0.85+0.5) and has correct format as numeric, another cell has
=INT(75/0.85+0.5) and is formatted as text - both cells show actual
values
(not formula). All the cells(over 1000) in this column have similar
content,
and all except TWO have correct numeric format.
I have tried, without success, various ways to change the 2 text cells
to
numeric .
I am baffled by this (why these 2 cells are different and why I cannot
change their format).
Any comments/suggestions ?
SM

If you wish, create a small file that exhibits this problem and send it to
me. If that's not possible, send me your actual file. Include as much
explanation as is necessary for me to find the cells with the problem. My
email is . Remove the "extra" from this
address. HTH Otto
"SM" wrote in message
...
Thanks for your comment.
Not only had I done your suggestion, I had also tried various
combinations:
deleted contents and formatted the whole column as numeric;
deleted contents and formatted the whole row as numeric;
deleted contents of the cell and several surrounding cells and formatted
as
numeric;
and several other combinations, which left me very frustrated.
In each case the (offending) cell would become formatted as numeric, but
as
soon as I typed in the formula, it would revert back to text format. No
other
cell would be affected.
Very strange and very baffling.
I can only guess some very higher setup option (unknown to mere mortals
like
me)needs to be adjusted.
SM


"Otto Moehrbach" wrote:

What is the format of those cells? If a cell is formatted as Text, clear
(erase) the cell. Then format the blank cell like you want. Then insert
the formula into the cell. Placing a number in a cell that is formatted
to
Text will not change the format. Format the cell when it is blank. HTH
Otto

"SM" wrote in message
...
Thanks for your response.
Unfortunately, any mathematical operation gives the resultant value -
does
not keep the formula used - I need to have the formula in the cells for
later
updates (individually).
For now, I have solved this problem by asking Excel to "ignore the
error" -
using the pop-up icon next to the cell.
Still would like to know why this error occurs and why it cannot be
solved
by conventional ways.
SM

"Otto Moehrbach" wrote:

The usual way is to perform a mathematical operation on the contents
of
the
cell. Select an out-of-the-way cell and enter a 1 into that cell.
With
that cell selected, do Edit - Copy. Now select the cells with the
problem.
Do Edit - PasteSpecial - Multiply. That should force a text number to
a
number. HTH Otto
"SM" wrote in message
...
In a column (Excel2007) that is correctly formatted as numeric, one
cell
has
=INT(15/0.85+0.5) and has correct format as numeric, another cell
has
=INT(75/0.85+0.5) and is formatted as text - both cells show actual
values
(not formula). All the cells(over 1000) in this column have similar
content,
and all except TWO have correct numeric format.
I have tried, without success, various ways to change the 2 text
cells
to
numeric .
I am baffled by this (why these 2 cells are different and why I
cannot
change their format).
Any comments/suggestions ?
SM