Сonvert numbers stored as text to proper numbers, or vice versa

You can never be sure that calculations give you correct results, unless you are sure that Excel cells have proper format – and numeric values are treated as numbers. You may find that some cells have an error saying that the numbers are actually stored as text. This means such numbers will be ignored in calculations. Or vice versa, sometimes you specifically want Excel to treat numbers as text.

The Convert Text to Number feature helps you remedy the issue in massive data sets seconds:

Convert Text to Number is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.

Download XLTools for Excel

When a cell with a numeric value has an error indicator in the upper-left corner – it means the number is actually stored as text. So Excel will treat it as text and ignore in calculations. That is why it's critical to convert such unrecognized numbers to proper number format.

1. Select the range which you want to convert.

2. Click the Text to Number button on XLTools ribbon Select Text to number from the drop-down list Done, each cell is formatted as number.

   See that the cells lose the error indicator.

When you want numeric values in cells to be treated as text, you can do this by adding an apostrophe (a single quote) in front of the value. This will effectively convert the numeric value to text format.

1. Select the range which you want to convert.

2. Click the Text to Number button on XLTools ribbon Select Number to text from the drop-down list Done, each cell is formatted as text.

   See that the cells get an indicator in the upper-left corner to remind you that the numeric value is actually stored as text.