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.

NOTE: 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
Fix Hidden Characters
Fix Hidden Characters With VBA
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
Download the Sample File

Look Like Numbers, But Don't Add Up

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

numbers treated as text

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

At the right, the COUNTA function is used in cell F4, and it shows that there are 4 entries in that range of cells.

However, the COUNT function in cell F5 show a result of zero -- none of the values in cells C3:C6 are recognized as numers.

Convert Text to Numbers with Paste Special

For some "text" numbers, you can fix the problem with Paste Special.

  1. Right-click a blank cell, and click Copy
  2. Select the cells that contain the "text" numbers
  3. Right-click on one of the selected cells, and click Paste Special
  4. In the Paste section, select Values
  5. In the Operation section, select Add
  6. Click OK
  7. numbers treated as text

After the numbers have been fixed, you can apply formatting, by using the Number Format commands on the Ribbon's Home tab.

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 4/5/14, you can try to convert them to real dates by replacing the slashes.

  1. Select the cells that contain the dates
  2. On the Ribbon's Home tab, click Find & Select (at the far right)
  3. Click Replace
  4. For Find what, type a forward slash:   /
  5. For Replace with, type a forward slash:   /
  6. Click Replace All
  7. numbers treated as text

  8. Click OK to confirm the replacement, then close the Find and Replace window .

After the dates have been fixed, you can apply formatting, by using the Number Format commands on the Ribbon's Home tab.

Fix Hidden Characters

If you copy data from a website, it might include hidden characters, such a non-breaking space. In Excel, this is character 160, and it is not fixed by some of the number cleanup techniques. You can search for that character, and replace it.

  1. Select the cells that contain the numbers to be fixed
  2. On the Ribbon's Home tab, click Find & Replace (at the far right)
  3. Click Replace
  4. For Find what, press the Alt key, and on the number keypad, press 0160 (nothing will appear in the box)
  5. For Replace with, leave the box empty
  6. Click Replace All
  7. numbers with character 160

  8. Click OK to confirm the replacement, then close the Find and Replace window .

Fix Hidden Characters With VBA

If you frequently need to remove the hidden non-breaking space character, you can use a macro.
Store the following macro in a workbook that is always open, such as your Personal Workbook. Then, add a button to an existing toolbar, and attach the macro to that button. To fix "text" numbers, select the cells, and click the toolbar button.

Sub CleanCode160()
Dim rng As Range
Dim arr As Variant
Dim i As Long
Set rng = Selection
'removes character 160
'non-breaking space
'from selected cells
If rng.Cells.Count = 1 Then
   ReDim arr(1 To 1, 1 To 1)
   arr(1, 1) = rng.Value
Else
   arr = rng.Value
End If

For i = 1 To UBound(arr, 1)
   arr(i, 1) = Replace(arr(i, 1), Chr(160), "")
Next i

rng.Value = arr

End Sub 

Convert Text to Numbers with Text to Columns

  1. Select the cells that contain the numbers
  2. On the Ribbon's Data tab, click Text to Columns
  3. In Step 1, select Delimited as the File type, then click the Finish button

    Text to Columns to fix numbers

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 might be able to paste the data as CSV.

  1. Copy the data in the other program
  2. Switch to Excel
  3. Right-click the cell where the paste will start, and click Paste Special
  4. Choose Edit>Paste Special
  5. Select CSV, if it appears in the list, and click OK

Convert Text to Numbers With VBA

If you frequently convert text to numbers, you can use a macro.
Store the following macro in a workbook that is always open, such as your Personal Workbook. Then, add a button to an existing toolbar, and attach the macro to that button. To fix "text" numbers, select the cells, and click the toolbar button.

Sub ConvertToNumbers()
'copies a blank cell outside the used range 
  Cells.SpecialCells(xlCellTypeLastCell) _
    .Offset(1, 1).Copy
'add the blank cell value (zero) to selected cells
'using Paste Special Add 
 Selection.PasteSpecial Paste:=xlPasteValues, _
     Operation:=xlPasteSpecialOperationAdd

'format selected cells
  With Selection
     .VerticalAlignment = xlTop
     .WrapText = False
  End With
  Selection.EntireColumn.AutoFit
End Sub 

Download the Sample File

Download the zipped file with the sample data and macros. The zipped file is in xlsm format, and contains macros, so enable them to test the code.

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: November 4, 2014 4:37 PM