Custom Format on 16 Digit Number

[Neil]

When I try to format a 16 digit number into 1111-2222-3333-4444 the last
number is displayed as a "0". How do I get the last number to display
correctly?

 

[Niek Otten]

Excel's precision for numerical entries is 15 decimal digits.
You can either format the cell as Text before entering the number (including
the dashes) or precede the entry with an apostrophe (which will not show in
the cell)
In both cases, it is not a number for Excel anymore, it is text. Probably,
that is what you require as well

 

[Chip Pearson]

Excel, like most software, rounds after 15 digits. If you do not need
to perform any math on the number (e.g., it is a credit card number),
preceed the text with an apostrophe. The apostrophe will not display
in the cell. Or, format the cell as Text before entering data.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Neil]

Niek:

I don't know of a way to format a text field to insert dashes automatically
does one exist?

 

[Bob Phillips]

Yes, something like

000-000-000

will do it, but as has been noted previously, Excel number precision is to
15 digits

HTH

Bob

 

[Dave Peterson]

Saved from a previous post:

If you type the value as text (either pre-format the cell or text and then do
the data entry or start your entry with an apostrophe), then you can use this
technique:

Type this in A1:
'1234123412341234

and use this in that helper column:
=mid(a1,1,4)&"-"&mid(a1,5,4)&"-"&mid(a1,9,4)&"-"&mid(a1,13,4)

or you could use a worksheet event that does the work for you--but you still
need to enter the data as text!

If you want to try this idea, rightclick on the worksheet tab that should have
this behavior. Select view code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myTempVal As Variant

On Error GoTo errhandler:

If Target.Cells.Count > 1 Then
Exit Sub
End If

If Intersect(Target, Me.Range("a:a")) Is Nothing Then
Exit Sub
End if

If IsNumeric(Target.Value) = False Then
Exit Sub
End If

myTempVal = CDec(Target.Value)
Application.EnableEvents = False
Target.Value = Format(myTempVal, "0000-0000-0000-0000")

errhandler:
Application.EnableEvents = True

End Sub


If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)