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