Link to home
Start Free TrialLog in
Avatar of lanman777
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?
Avatar of etech0
etech0
Flag of United States of America image

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
Avatar of andrewssd3
andrewssd3
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Open in new window

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