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        :

Last Reviewed: March 11, 1999