lanman777

asked on

# Office 2010 and Excel 2010 issue. Zero's being put on end of field's with long numbers

When I enter a number in spreadsheet with 16 numbers, no matter what I enter as the 16th character it always turns it into a zero.

First question: Is there a fix?

Second: If not, I need to create a variable to search for any entries that have 16 numbers and the last digit is a zero. How can I do that?

First question: Is there a fix?

Second: If not, I need to create a variable to search for any entries that have 16 numbers and the last digit is a zero. How can I do that?

Is this a problem in one spreadsheet, or in any? Try doing this in a new excel workbook and see what happens.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

One way to find them would be like this, using VBA. This assumes you have selected the range of cells you want to search in. It selects all the cells that are greater than 999,999,999,999,999 - these will all be truncated

```
Public Sub FindTruncatedNumbers()
Dim c As Excel.Range
Dim rngFound As Excel.Range
Dim vValue As Variant
For Each c In Selection
If Not IsError(c.Value) Then
vValue = c.Value
If IsNumeric(c.Value) Then
If vValue > 999999999999999# Then
If Not rngFound Is Nothing Then
Set rngFound = Application.Union(rngFound, c)
Else
Set rngFound = c.Cells(1)
End If
End If
End If
End If
Next c
If Not rngFound Is Nothing Then
rngFound.Select
End If
End Sub
```

You could add another condition if you only wanted the 16 digit numbers (not larger)