Hints - Microsoft Excel - Fix Excel Numbers That Don't Add Up

Some Excel values look like numbers, but don't add up, because Excel thinks they are text. With the techniques in the article, you can convert text "numbers" to real numbers. Then, 1 + 1 will equal 2, instead of zero.

Look Like Numbers, But Don't Add Up

If you copy data from another program, such as Microsoft Access, or from a text file, Excel may treat the numbers. In Excel, the values look like numbers, but they don't act like numbers, and don't show a correct total, as you can see below.

In the screen shot above, the values in column B looks like numbers, but they don't add up -- the total is zero.

Convert Text to Numbers with Paste Special

Convert Dates with Replace All

If dates are formatted with slashes, such as 5/5/04, you can convert them to real dates by replacing the slashes.

Convert Text to Numbers with Text to Columns

Convert Trailing Minus Signs

In Excel 2002, and later versions, imported numbers with trailing minus signs can be easily converted to negative numbers. 

Note: If 'Trailing minus for negative numbers' is checked, you can click Finish in Step 1 of the Text to Columns wizard. 

Convert Trailing Minus Signs - Formula

In the formula, the RIGHT function returns the last character in cell A1. 

If that character is a minus sign, the VALUE function returns the number value to the left of the trailing minus sign.

The minus sign before the VALUE function changes the value to a negative amount. 

Convert Trailing Minus Signs Programmatically

In all versions of Excel, you can use the following macro to convert numbers with trailing minus signs. 

Paste as CSV

To prevent copied numbers from being pasted as text, you may be able to paste the data as CSV.

Convert Text to Numbers With VBA

If you frequently convert text to numbers, you can use a macro.
Add a button to an existing toolbar, and attach the macro to that button. Then, select the cells, and click the toolbar button.