Three Ways to Convert Text-Based Numbers to Values

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.
 
Read more articles by David Ringstrom. 
 
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.

 

You may like these other stories...

You probably don't want to think about how many times you access the File menu in Excel 2010 or 2013. Personally I think Excel 2010 has the best possible File menu arrangement, other than having Print Preview grafted...
Following other recent high-profile hacking events, investigators discovered yesterday that hackers broke into the draft work paper files of several famous CPA firms. Revealing images of the scantily clad documents have been...
For bitcoin users, the taxman cometh. And you best know how to calculate taxes owed on what the IRS calls convertible virtual currency.In March 2014, the IRS issued Notice 2014-21, which declares virtual currency will be...

Already a member? log in here.

Upcoming CPE Webinars

Sep 24
In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.
Sep 30
This webcast will include discussions of important issues in SSARS No. 19 and the current status of proposed changes by the Accounting and Review Services Committee in these statements.
Oct 21
Kristen Rampe will share how to speak and write more effectively by understanding your own and your audience's communication style.
Oct 23
Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.
Looking for AccountingWEB UK?