Microsoft Excel 2007 with Example

4 Format Cells:

4.5 Format Cells: Text to Numbers

By default, text is left-aligned and numbers are right-aligned. This example teaches you how to convert text strings that represent numbers to numbers.

Text to Numbers in Excel

1. Select the range A1:A4 and change the number format to General.

General Format

2. Numbers preceded by an apostrophe are also treated as text. Select cell A5 and manually remove the apostrophe.

Remove Apostrophe

3a. You can also combine step 1 and 2 by adding an empty cell to the range A1:A5. By doing this, you let Excel know that these text strings are numbers. Copy an empty cell.

Copy Empty Cell

3b. Select the range A1:A5, right click, and then click Paste Special...

3c. Click Add.

Add Empty Cell

3d. Click OK.

Result. All numbers are right-aligned and treated as numbers.

Text to Numbers Result

4a. You can also use the VALUE function.

Value Function

4b. Here's another example. Use the Right function (or any other text function) to extract characters from a text string and then use the VALUE function to convert these characters to a number.

Right Function