Excel: Numbers Not Formatting, Functions Will Not Work
ID: Q26967
|
The information in this article applies to:
-
Microsoft Excel for the Macintosh, versions 1.x, 2.2, 3.0, 4.0
SUMMARY
When you import information into Microsoft Excel, numbers may not
format correctly and formulas may not calculate properly if the file
was saved in TEXT format, rather than SYLK format, because the numbers
are being read as text.
To see if this is the problem, click in a cell that contains a number.
Position the insertion point in front of the number and press the
BACKSPACE key. If you were able to delete a space, the number was
text. That particular number should now format correctly and any
functions that reference that cell should now work.
The VALUE(text) function can be used to convert the text string to
numbers, as follows:
- Insert a blank column to the right of the text.
- In the first cell on the blank column, enter the formula
=VALUE(cell reference)
where "cell reference" is the reference to the cell containing the
text.
- Highlight the rest of the column, including the cell in which the
formula was entered.
- From the Edit menu, choose Fill Down.
The column will now contain numbers, not text, and these numbers can
be formatted.
Another way to convert numbers that have been entered as text into
values is to use the Paste Special command on the Edit menu, as
follows:
- Enter the number 1 in a blank cell on the worksheet.
- Select the cell containing the 1 and choose Copy from the Edit
menu.
- Select the range of cells that contain numbers that have been
entered as text.
- From the Edit menu, choose Paste Special.
- Select Multiply and click OK.
Additional query words:
Keywords :
Version :
Platform :
Issue type :
|