Excel tip: Convert text numbers to values

Kashflow logo
4

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

Please Login or Register to read the full article

The full article is available to registered AccountingWEB.co.uk 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.

David H Ringstrom
Columnist
Share this content

Replies

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)