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