15 digit limit

[Erin]

Hi All,

Regardless of the number of digits displayed, Excel stores
numbers with up to 15 digits of precision. If a number
contains more than 15 significant digits, Excel converts
the extra digits to zeros (0).

Can you override this (like in the case of custom
formatting ####-####-####-#### or 0000-0000-0000-0000 for
credit card numbers in a column?

I realize I could use text, but then it would be necessary
to enter the dashes with each entry. Any ideas? Thanks

 

[J.E. McGimpsey]

No, you can't override it. You might ask whether you really need to
have the dashes, or you could use an event macro to change the text
string (could be useful to validate the checksum, too).

 

[JohnI in Brisbane]

Erin,

Someone may have a better suggestion.

I would enter the card numbers into a cell as text by-

either formatting the cells as text, or entering a single quote at the
beginning of each card number.

then in a separate column enter the following formula to format the data-

=LEFT(A2,4)&"-"&MID(A2,5,4)&"-"&MID(A2,9,4)&"-"&RIGHT(A2,4)

then "Copy", "Paste Special -Values" to remove the formulas.

regards,

JohnI

 

[Harlan Grove]

...
...

I would enter the card numbers into a cell as text by-

either formatting the cells as text, or entering a single quote at the
beginning of each card number.

This is the best way to deal with entry.

then in a separate column enter the following formula to format the data-

=LEFT(A2,4)&"-"&MID(A2,5,4)&"-"&MID(A2,9,4)&"-"&RIGHT(A2,4)

then "Copy", "Paste Special -Values" to remove the formulas.

...

Maybe better to automate this if the OP doesn't mind VBA. If the range in which
credit cards were entered were named CCNEntry, then the following event handler
in the ThisWorkbook class module would automatically add the dashes.


Private Sub Workbook_SheetChange( _
ByVal Sh As Object, _
ByVal Target As Excel.Range _
)
'----------------------------------

If Intersect(Target, Names("CCNEntry").RefersToRange) Is Nothing Then Exit Sub

If Not Target.Text Like "####-####-####-####" And IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target.Formula = Format(CDec(Target.Formula), "0000-0000-0000-0000")
Application.EnableEvents = True
End If

End Sub

 

[Erin]

Thanks all. That is pretty much what I suggested to the
user, but I wanted to make sure I wasn't missing
anything. Thanks for all your suggestions.