7. Excel How to convert text to number ?

Normally, when you write a number in a cell, Excel recognises it as a number. However, you may have cells with numbers that Excel considers as text.

If you select such a cell, you will see in the Formula bar that the number is preceded by an apostrophe. You can remove the apostrophe from the formula bar and the text is converted to a number.

Excel 2016 Converting Text to Number

If you have a cell range with text to convert to numbers, then there are 3 possible solutions:

Solution 1

Cells with numbers stored as text are marked with a green triangle in the top left corner.

Excel 2016 Converting Text to Number

And when you select a range of cells with numbers stored as text, a small yellow diamond appears.

Click on this little diamond and choose Convert to Number from the menu that appears.

Excel 2016 Converting Text to Number

NB. The selected range can contain cells with other data types, but the selection must start from a cell with a number stored as text.

Solution 2

Select the cell range containing the texts to be converted to numbers. Use the Text to Columns command from the Data Tools group on the Data tab of the Ribbon.

Excel 2016 Text to Columns command

This starts the Excel Convert Text to Columns Wizard. Click on the Finish button right away.

Solution 3

I'll give you a tip in case the two solutions above don't work. Let's take the following example :

Excel 2016 Converting Text to Number

Enter the following formula in cell B2 and copy it to column B:

= A2 * 1

The values in column B are numbers. You can copy and paste them into column A with the Values option. Then delete the values in column B.

Excel 2016 Paste Values

<< 6. Excel How to freeze multiple rows ?

8. Excel How to calculate days between 2 dates ? >>

Reader comments

Add a comment