Sometimes you may type numbers in cells that were formatted as text which later can cause problems when you perform calculations with them. The numbers that are formatted or stored as text can also produce confusing sort orders.
The numbers that are formatted as text are left-aligned rather than right-aligned. With Error Checking turned on, you can see an error indicator marking with them. You can convert text numbers (the numbers that are stored as text) to numbers in a cell or range of cells at any one time or in multiple nonadjacent cells or ranges of cells all at once.
- Open Microsoft Office Excel 2007, click Office Button, and then click Excel Options button.
- On the Excel Options dialog box, click Formula tab, and then follow all steps below:
1. Under Error Checking, make certain that the Enable background error checking check box is checked.
2. Under Error Checking Rules, make certain that the Numbers formatted as text or preceded by an apostrophe check box is selected.
3. Click Ok button.
- Select the cell or range of cells that has an error indicator in the upper-left corner . Note that all cells that you select must be adjacent cells.
- Next to the selected cell or range of cells, click the error button that appears , and then click Convert to Number.
- On the worksheet, select a blank cell and format it as General.
To format cell, do the following:
▫ On the Home tab, in the Number group, click drop-down arrow of the Number Format box, and then click General. Or you can click Dialog Box Launcher next Number, and then click General in the Category box.
- Enter 1 in the cell, and then press Enter.
- Select the cell, and then on the Home tab, in the Clipboard group, click Copy.
Or you can use keyboard shortcut by pressing Ctrl +C.
- Select the nonadjacent cells or ranges of cells that contain text numbers which you want to convert.
- On the Home tab, in the Clipboard group, click the arrow under Paste, and then click Paste Special.
- In the Paste Special dialog box, below Operation, select Multiply, and then click Ok button.
Note: To delete the number that you typed into the cell in step 2 after you converted all of the text numbers successfully, select that cell, and then press Delete.
This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages: