Notice of Maintenance on October 9th. Click here for further details.

3 easy ways to convert text to numbers

1 minute read time.

Sometimes when importing data from another program into Microsoft® Excel®, the numbers are stored as text which causes havoc in your report. In some instances, the simple method of changing the format of the column to numbers will work, but only on the most basic numbers formatted as text. In the below examples you will note that the numbers are displayed as text by the small green triangles appearing in the top left corner.

Here are 3 ways to convert your data starting with the most favoured method.

  1. Paste Special – Multiply

This method can be used to easily convert text to numbers by multiplying them by a number – usually 1 so that the values don’t change.

In our example, enter a 1 in the A1 cell.  With A1 still selected, copy the cell either by using Ctrl + C or by selecting Copy from the Clipboard section under the Home menu.

 

Select the cells containing the text to be converted, right click, select the arrow to the right of Paste Special and select Paste Special

In the Paste Special dialogue box, select Multiply and then OK.

 

The data has been converted to numbers and can be formatted

 

  1. Using Excel’s built-in help.

(This method is not recommended when using a very large data range)

 Select the relevant cell range, i.e. A3:A12

 Notice the small box with the exclamation to the right of the first cell.  Hover over the box, click the dropdown arrow and then select Convert to Number

The text data is now converted to numbers.  Notice that the green triangles in the top left corner are no longer visible and the numbers are now on the right hand side of the cell.

  1. Text to Columns

 Select the relevant cell range, i.e. A3:A12

On the menu, go to Data and under Data Tools select Text to Columns

As no changes are required in the Convert Text to Columns Wizard, select Finish

 

It is important to now select the numbers again, go the Home menu and format the number into the required format.