Leading the way in Microsoft Office Development

Changing Text to Numbers

Sometimes numbers can look like numbers when they are infact text.

All numbers should be right aligned by default

If numbers are left aligned then they are probably being interpreted as text.

It is not possible to perform calculations on any numbers that are being interpreted (or formatted) as text

SS

Example SUM

This often happens when you import data from Access

Identifiying Numbers formatted as Text

(Tools > Options)(Error Checking tab, "Number stored as text")

Excel will frag any cells that contain numbers that are formatted as text

A green flag will be displayed in the top left corner click menu button and select "Convert to Number"

1) Multiple the values by 1 using Paste Special

Never Change the Alignment

If the alignment is not changed, then only numbers aligned on the right will be used in numerical calculations.

To avoid any unpredictable results in your formulas never manually change the alignment, this way you can easily spot any numerical values that are being interpreted as text.

If you want to have a currency symbol appear before a numerical value then you need to apply the relevant number format.

Multiply by 1

When you copy or import data from other applications the numbers are often inserted as text.

All the values in column B are being treated as text. You know this from their alignment.

Text is always aligned on the left. Numerical values are always aligned on the right when using the "General" number format.

The formula is cell D2 refers to cells B2 and B3 and returns the correct result, which is 50.

However the formula in cell D7 which uses the SUM() function and refers to the range of cells "B7:B10" does not return the correct result.

You can easily convert the values in column B to actual numbers by multiplying them by 1. See screen shot below.

Enter the number 1 in cell D10. This can actually be placed in any cell.

Select cell D10 and press (Edit > Copy).

Select the range of cells containing the values (in this case "B2:B10") and select (Edit > Paste Special).

Choose the Multiply option button and press OK. This will multiply whatever is on the clipboard (in this case the value 1) to the highlighted range.

Once the values have been converted to numbers all worksheet functions will return the correct results.

You can also convert text values to numerical values by using the Text Import Wizard. For more details please refer to the Using Text To Columns page.