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

How to convert numbers stored as text to proper number format in Excel with XLTools add-in

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 numbers stored as text to numbers
  • Convert numbers to text format

Before you begin, add Convert Text to Number to Excel

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.

Get started with XLTools ribbon
– free trial gives you 14 days of full access to all features.

How to convert numbers stored as text to proper numbers

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.

See result: numbers stored as text are converted to proper number format

How to convert numbers to text format

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.

See result: numbers are converted to text format by adding an apostrophe
Any questions or suggestions?
How can we help?