Search Contextures Sites

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

For instructions on

changing written words to numbers(e.g. from Three to 3), seeWords to Numbers in Excel.Look Like Numbers, But Don't Add Up

Convert Text to Numbers with Paste Special

Convert Dates with Replace All

Convert Text to Numbers with Text to Columns

Convert Trailing Minus Signs

Convert Trailing Minus Signs - Formula

Convert Trailing Minus Signs Programmatically

Paste as CSV

Convert Text to Numbers with VBA

## 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 as text.

*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

- Select a blank cell
- Choose Edit>Copy
- Select the cells that contain the numbers
- Choose Edit>Paste Special
- Select Add
- Click OK
- To apply number formatting, choose Format>Cells
- On the Number tab, select the appropriate format, then click OK.

Watch the VideoTo see the steps described above, you can watch this short video tutorial.

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

- Select the cells that contain the dates
- Choose Edit>Replace
- For Find what, type a forward slash: /
- For Replace with, type a forward slash: /
- Click Replace All
- To apply date formatting, choose Format>Cells
- On the Number tab, select a date format, then click OK.

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

- Select the cells that contain the numbers
- Choose Data>Text to Columns
- To view the Trailing Minus setting, click Next, click Next
- In Step 3, click the Advanced button
- Check the box for 'Trailing minus for negative numbers', click OK
- Click Finish
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

Thanks to Bob Ryan, from Simply Learning Excel, who sent this formula to fix imported numbers with trailing minus signs.

- In this example, the first number with a trailing minus sign is in cell A1
- Select cell B1, and enter this formula:
- =IF(RIGHT(A1,1)="-",-VALUE(LEFT(A1,LEN(A1)-1)),VALUE(A1))
- Copy the formula down to the last row of data.
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.

Sub TrailingMinus() ' = = = = = = = = = = = = = = = = ' Use of CDbl suggested by Peter Surcouf ' Program by Dana DeLouis, dana2@msn.com ' modified by Tom Ogilvy ' = = = = = = = = = = = = = = = = Dim rng As Range Dim bigrng As Range On Error Resume Next Set bigrng = Cells _ .SpecialCells(xlConstants, xlTextValues).Cells If bigrng Is Nothing Then Exit Sub For Each rng In bigrng.Cells If IsNumeric(rng) Then rng = CDbl(rng) End If Next End Sub## Paste as CSV

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

- Copy the data in the other program
- Switch to Excel
- Select the cell where the paste will start
- Choose Edit>Paste Special
- Select CSV, click OK

## 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.Sub ConvertToNumbers() Cells.SpecialCells(xlCellTypeLastCell) _ .Offset(1, 1).Copy Selection.PasteSpecial Paste:=xlPasteValues, _ Operation:=xlPasteSpecialOperationAdd With Selection .VerticalAlignment = xlTop .WrapText = False End With Selection.EntireColumn.AutoFit End Sub*For Excel 2002, the problem with Access data has been fixed in Office XP Service Pack 3.

There is information in the following MSKB article:

Numbers that are copied from Access 2002 paste as text in Excel2002

http://support.microsoft.com/default.aspx?id=328933## More Data Entry Tutorials

1. Data Entry -- Tips

2. Data Entry -- Fill Blank Cells

3. Data Entry -- Convert Text to Numbers

4. Data Entry -- Increase Numbers With Paste Special

____________

Contextures Inc., Copyright ©2014

All rights reserved.

Last updated: April 13, 2014 2:15 AM* *