Search Contextures Sites

 

More Data Entry Links

Data Entry -- Tips

Fill Blank Cells

Increase Numbers With Paste Special

 

Contextures
Excel news
by email

 

 

Learn how to create Excel dashboards.

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

 

 

Learn how to create Excel dashboards.

 

 

30 Excel Functions in 30 Days

 

 

Learn how to create Excel dashboards.

 

 

Learn how to create Excel dashboards.

 

 

Live-link your Excel dashboards to nearly any web data.

 

 

 

Learn how to create Excel dashboards.

 

 

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), see Words 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

  1. Select a blank cell
  2. Choose Edit>Copy
  3. Select the cells that contain the numbers
  4. Choose Edit>Paste Special
  5. Select Add
  6. Click OK
  7. To apply number formatting, choose Format>Cells
  8. On the Number tab, select the appropriate format, then click OK.

Watch the Video

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

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

 

Convert Text to Numbers with Text to Columns

  1. Select the cells that contain the numbers
  2. Choose Data>Text to Columns
  3. Click Finish

Convert Trailing Minus Signs

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

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

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

  1. Copy the data in the other program
  2. Switch to Excel
  3. Select the cell where the paste will start
  4. Choose Edit>Paste Special
  5. 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 Excel 2002  
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

Excel Data Entry Videos

____________

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search

Search Contextures Sites

 

 

 

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: October 22, 2014 7:47 PM