Microsoft Excel 2007 tutorial: convert text numbers to numbers


Convert text numbers to numbers

VI. Convert text numbers to numbers

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 Excel 2007 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.

VI.1. Convert text numbers to numbers in a cell or range of cells

- Open Microsoft Office Excel 2007, click Office Button, and then click Excel Options button.

Excel 2007 Excel Options

- 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.

   Excel 2007 Error Checking

   2. Under Error Checking Rules, make certain that the Numbers formatted as text or preceded by an apostrophe check box is selected.

  Excel 2007 Error Checking Rules

   3. Click Ok button. 

- Select the cell or range of cells that has an error indicator in the upper-left corner Excel 2007 error indicator . 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 Excel 2007 Error button, and then click Convert to Number.

Excel 2007 Convert to Number

VI.2. Convert text numbers to numbers in multiple nonadjacent cells or ranges of cells

- 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.

   Excel 2007 Arrow with Dialog Box Launcher 

- Enter 1 in the cell, and then press Enter.

- Select the cell, and then on the Home tab, in the Clipboard group, click Copy.

Excel 2007 Clipboard group

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.

Excel 2007 Paste Special option

- In the Paste Special dialog box, below Operation, select Multiply, and then click Ok button.

Excel 2007 Paste Special dialog box

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.

 


Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.