Share this content

Excel tip: Convert text numbers to values

11th Sep 2013
Share this content
Kashflow logo

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.

Home tab Text indicator - figures in text won't work in Excel formulae

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:

=ISTEXT(A1)

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.

Use VALUE function to convert numbers stored as text

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.

Convert numbers stored as text to values by multiplying the cells by 1.

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.

Text to Columns will convert text numbers to values as the command default.

"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.

Replies (4)

Please login or register to join the discussion.

avatar
By Shreena
16th Sep 2013 10:34

Brilliant! Thank you. 

Brilliant! Thank you. 

Thanks (0)
avatar
By jndavs
26th Sep 2013 17:02

or....

I expect someone will mention ASAP utilities before long.

http://www.asap-utilities.com/

Thanks (0)
avatar
By Briar
26th Sep 2013 16:20

Another Thank You

Having recently downloaded what I thought were dates and numbers (from a bank system), I found them to be in text format. This article solved the problem of how to convert the useless texts to dates and numbers.

Thanks (0)
avatar
By afs
12th Apr 2014 06:50

text to values

the data in the cost column of an excel spreadsheet (telephone bill) downloaded from a telephone  are in the form   currency symbol £ followed by space and then number to two decimal places

istext returns "true" for the  data  

summing the data returns 0

value formula returns #value

none of the methods described in the article work to convert text to values - Mac 2011

 

 

 

 

 

 

 

 

Thanks (0)