By David Ringstrom, CPA
Periodically, you may encounter numbers in Excel that you can’t sum or use arithmetically. A common cause for this is numbers formatted as text. Often, reports exported from other programs, such as an accounting package, will be formatted as text or they might contain embedded spaces.
In this article, I’ll describe three ways you can convert numbers that appear trapped under glass into a usable format.
First, there are a couple of ways to determine if your numbers are formatted as text. Select one or more of the suspect values and then in:
- Excel 2007 and later or Excel:Mac 2011: Determine if the word Text appears on the Home tab, as shown in Figure 1.
- Excel 2003 and earlier: Choose Format, Cells, and then determine if the Number tab is set to Text.
Figure 1: The Text format prevents you from using numbers with mathematical functions.
The Text format in a cell displays the contents of a worksheet cell rather than its result. Thus, if you enter a formula in a cell formatted as text, the underlying formula will appear in your worksheet cell rather than the result.
However, the 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.
You can categorically determine if a number is stored as text by way of the ISTEXT worksheet function. For instance, if cell A1 contains a value you think may be stored as text, type this formula in a nearby blank cell:
=ISTEXT(A1)
The ISTEXT function will return TRUE if the value in cell A1 is being stored as text, or FALSE if it isn’t.
Now that we know how to determine if a number is stored as text, let’s look at three ways to convert one or more cells back to numeric values:
One approach is to use the =VALUE function, as shown in Figure 2. If the cell that you reference with the VALUE formula can be converted to a numeric value, you’ll see the corresponding number. Otherwise, you’ll see a #VALUE! error, which signifies 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. Keep in mind that you’ll then need to copy these formulas to the clipboard, and then use Paste Special, Values to preserve the numeric results. You might choose to replace the original set of numbers with the results from the VALUE formulas, after which you can clear the VALUE formulas from the spreadsheet.
Figure 2: You can use the VALUE function to convert numbers stored as text to usable values.
A somewhat simpler approach involves using the Paste Special, Multiply command. 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.
Figure 3: You can also convert numbers stored as text to values by multiplying the cells by 1.
The easiest way to convert a range of values to text, though, involves using the Text to Columns feature. In any version of Excel, select the range of cells that contain numbers stored as text, then choose Data, Text to Columns, and then click Finish. Don’t select any choices within Text to Columns; simply launch the wizard and then click Finish, as shown in Figure 4. Your text-based numbers will be usable numbers in Excel.
Figure 4: The Text to Columns wizard is the easiest way to convert numbers stored as text to values.
About the author:
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
