Select the product you need help with

Unexpected behavior in Excel for Mac with numbers formatted as text

Article ID: 191007 - View products that this article applies to.

This article was previously published under Q191007

SYMPTOMS

If a number is formatted with the Text number format, and you attempt to change the format to something other than Text, the number may not be changed as you expect.

Back to the top | Give Feedback

CAUSE

When you format a cell using the Text number format, the cell is changed and anything that is entered is displayed as text.

Back to the top | Give Feedback

WORKAROUND

To work around this behavior, use any of the following methods to reenter the numbers in the reformatted cells.

Method 1: Retype the Numbers

After you change the format, retype the numbers, or click in the formula bar and then press RETURN.

Method 2: Select Edit Directly In Cell

To use this method, follow these steps:

Click Preferences on the appropriate menu in the following list:
- The Tools menu in Microsoft Excel 98 for Mac.
- The Edit menu in Microsoft Excel 2001 for Mac.
- The Excel menu for Microsoft Excel X for the Mac or a later version.
Click the Edit tab, and then verify that the Edit directly in cell check box is selected.
Double-click the cell that you want to format, and then press RETURN.

Method 3: Use a Microsoft Visual Basic for Applications Procedure

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. Create a Visual Basic for Applications macro to reenter the numbers in the selected cell or range of cells. Type the following code in a module sheet:

   Sub Enter_Values()
      For Each xCell In Selection
         xCell.Value = xCell.Value
      Next xCell
   End Sub

Before you run this procedure, switch to the worksheet that contains the numbers you want reentered, select the range of cells that contain those numbers, apply a number format other than Text, and then run the macro.

Method 4: Use Paste Special

To use this method, follow these steps:

Type the value 1 in a blank cell on the worksheet.
Select the cell that contains the value 1 and click Copy on the Edit menu.
Select the range that contains the numbers that were formatted as text. Click Paste Special on the Edit menu, click Multiply under Operation, and click OK.
If you have to, change the cell format of the selection to a number format other than Text.

Back to the top | Give Feedback

MORE INFORMATION

To format a number as text, follow these steps:

Select a single cell or a range of cells.
On the Format menu, click Cells.
On the Number tab, click the Text category, and click OK.
In the cell or range of cells selected, type numeric values.

After following the steps above, you cannot change the format back to a number without reentering the numbers.

Note If numbers are entered before the cells are formatted to use the Text number format, they remain numbers, but they are aligned to the left in the cell to appear as text.

Back to the top | Give Feedback

REFERENCES

Excel X and later versions

For more information about how to format cells as text, click Excel Help on the Help menu, type format existing numbers as text, click Search, and then click a topic to view it.

Excel 2001

For more information about formatting cells as text, click the Office Assistant, type format existing numbers as text, click Search, and then click a topic to it.

Note If the Assistant is hidden, click the Office Assistant button on the standard toolbar.

Excel 98

For more information about formatting cells as text, click the Office Assistant, type format numbers text, click Search, and then click Format existing numbers as text to view the topic.

Note If the Assistant is hidden, click the Office Assistant button on the standard toolbar. If Microsoft Help is not installed on your computer, click the following article number to view the article in the Microsoft Knowledge Base:

179216

(http://support.microsoft.com/kb/179216/ )

OFF98: How to Use the Microsoft Office Installer Program

Back to the top | Give Feedback