Excel tip: Convert text numbers to values
AccountingWEB.com contributor David Ringstrom steps through some shortcuts to deal with a common Excel irritation, where the spreadsheet won’t recognise figures because they're not in the right format.
You may occasionally encounter numbers in Excel that refuse to sum or work in any other Excel formula. Most often this is because your numbers are formatted as text. Reports exported from other programs such as accounting systems are often formatted as text, or may arrive with embedded spaces.
There are three ways to turn these text numbers into a usable format; this tips article will explain all three.
Depending on which version of Excel you use, there are a couple of ways to diagnose whether your figures are in text format:
- For Excel 2007 and later or Excel (also Mac 2011 edition): check to see if the word “Text” appears on the Home tab, as shown below.
- For Excel 2003 and earlier, pick the Format-Cells menu command and see if the Number tab is set to Text.
The Text format in a cell displays the contents of a worksheet cell rather than its result. So if you enter a formula in a cell formatted as text, the underlying formula will appear in your worksheet cell rather than the result.<
Text format is not the only way to store values as text. If you look closely within a worksheet cell, you may see that a numeric value is prefaced with a single quote. This is another means to display the contents of a cell rather than the result.
The ISTEXT worksheet function is the ultimate tool to determine if a number is stored as text. If cell A1 contains a value you think may be stored as text, type this formula in a nearby blank cell:
The ISTEXT function will return TRUE if the value in cell A1 is stored as text, or FALSE if it isn't.
Having determined that you are dealing with a text value, here’s what to do about it. The =VALUE function, as shown in the figure below is one approach. If the cell your VALUE formula refers to can be converted to a numeric value, it will return the corresponding number. Otherwise, you'll see a #VALUE! error, which indicates that the referenced cell contains letters or other non-numeric contents. You can then copy the VALUE formula as needed to convert additional values to numbers.
Bear in mind that you’ll have to copy these formulas to the clipboard, and then use Paste Special-Values to preserve the numeric results. You could also replace the original set of numbers with the results from the VALUE formulas, after which you can clear the VALUE formulas from the spreadsheet.
However, the Paste Special-Multiply command presents a simpler approach. In any version of Excel, enter the number 1 in a blank cell and then copy that value to the clipboard. Next, select the range of values stored as text, right-click, and choose Paste Special, Multiply, and then OK. You can then clear 1 from the worksheet cell.
But the easiest way to convert a range of values to text is to use the Text to Columns command, which works in the same way for all Excel versions. First, choose the cells that contain numbers stored as text, then pick Data-Text to Columns and then click Finish.Don’t select any menu choices within Text to Columns; simply launch the wizard and then click Finish, as shown below. Your text-based numbers will be usable numbers in Excel.
"Either you work Excel, or it works you!" says David Ringstrom CPA, the head of Atlanta-based software and database consultancy Accounting Advisors. He presents Excel training webcasts for CPE Link and contributes articles on Excel to AccountingWEB and Microsoft Professional Accountant's Network newsletter. He can be reached by email at david[AT]acctadv.com. More Excel tips from David H Ringstrom available here.