Formatting Numbers as text

  • Thread starter Thread starter John
  • Start date Start date
J

John

We are having a problem with numbers stored as text in Excel 2002.

If a cell is explicitly formatted as text, and a number is inserted; then
automatic error checking inserts a comment indicating that the number is
stored as text and offering to fix it. The fact that this is not an error
and the field is actually a text field containing numeric information is
immaterial to Excel, it wants to fix the problem for us. On a bright note,
however, is that it recognizes the field as text.

If a cell is not explicitly formatted as text, and a number is inserted;
then Excel assumes that it is a number. If one then selects the cell and,
via the Format Cells dialog, set the format explicitly to text, the number
assumes the left-justified position that would indicate it is text, but
automatic error checking does not indicate an error. It appears that Excel
is still treating this as a numeric value, since, when we try to move it
into a database we get a type error.

If one then puts a leading apostrophe in the field, automatic error checking
suddenly wakes up and realizes the field is a number stored as text, and it
is treated as text.

This cannot be by design.

Has anyone else run in to this behaviour?

Thanks,
John
 
Hi John
this is just the way Excel behaves :-)
You can disable the error showing in the options dialog
 
Just because you changed the format of the cell doesn't mean that you changed
the value and numeric 1234 is different than text "1234".

Try everything you did with data in A1.

Put =istext(a1) and =isnumber(a1) in two adjacent cells.

Especially try the start as General, then format as Text. Note the results of
the functions.

Now click on A1, hit F2 (pretending to edit it) and then hit enter.

Reentering the value does lots.

=======
and if you're looking for a way to convert those text numbers to number numbers:

select an empty cell
copy it
select your range of text numbers
Edit|paste special|click Add
 
Hi John

I would avoid Text formatted cells as much as possible. They often end
up causing LOT's of problems. The main one being, ANY cell the
references a text formatted cell will take on the Text format itself.
This means, then next time it recalculates the cell will show the
formula and NOT the result.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
Huh?

This has *never* happened to me - recalculation doesn't change
formatting...
 
I've never seen this change happen with a simple recalculation.

I have see it in simple formulas that refer to a cell that is formatted as
text. Editing that formula (even F2|Enter) will allow excel to "help" out and
copy the number format (text) to the formula cell (shouldn't be text).
 
Format any cell as Text, say A1

Enter any number in A1

In B1 enter =A1+1

Now check the format of cell B1. It is Text.

Now select B1 and re-enter (recalculate). You will now only see the
formula and not the result.

Quite a common problem.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
I don't think I'd classify re-entering the formula as a recalculation.

I formatted A1 as text
I put =a1+1 in B1
I put 3 in A1
The worksheet recalculated and B2 changed to 4 (it didn't show the formula after
the recalculation).

Re-entering of the formula causes problems. Recalculation doesn't.
 
Perhaps, however, re-entering a formula forces a recalculation of it.
I'm not going to split hairs with you over this. The fact is the Text
formatted cells are a common cause of problems for Excel users.

Like with array formulas, I think common problems ascociated with
certain functions, formats etc should be pointed out.



** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***
 
Back
Top