Excel tip: Convert text numbers to values

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

Continued...

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.

Comments

Brilliant! Thank you. 

Shreena | | Permalink

Brilliant! Thank you. 

jndavs's picture

or....

jndavs | | Permalink

I expect someone will mention ASAP utilities before long.

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

Another Thank You

Briar | | Permalink

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.

text to values

afs | | Permalink

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