Problem with custom format

[theSizz]

Should be simple solution but I can't figure it out. I want a user to b
able to enter a 16 digit number in a cell and have the cell display a
follows. 1234-1234-1234-1234

I applied this custom format to the cell ####-####-####-####

When the number is entered as 1234123412341234. The cell display
1234-1234-1234-1230. The last digit is changed from a 4 to a zero
What's going on?? It doesn't matter what sequence of 16 digit number
you enter, the last digit is always changed to a zero. By the way th
entry doesn't have to be treated as numeric it could be text also.

Any help would be appreciated.

Thanks,
theSiz

 

[Bob Phillips]

It's not the format, it's the value. Type in 1234123412341234 and you will
see it change to 1234123412341230.

15 digits is the limit in entering a number in Excel.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Lloyd H. London]

It dropped the last digit, or rather rounded it, because Excel, like
many applications uses Double Precision Floating Point math.

 

[Dave Peterson]

So you can avoid the problem by entering the dashes (making the cell text).

Or you could format the cell as Text
(or start with a leading apostrophe, like: '1234123412341234)
and have an event macro put the dashes in for you.

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
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

If Len(Target.Value) = 16 Then
If IsNumeric(Target.Value) Then
myTempVal = CDec(Target.Value)
Application.EnableEvents = False
Target.Value = Format(myTempVal, "0000\-0000\-0000\-0000")
End If
End If

errhandler:
Application.EnableEvents = True

End Sub

I used column A as my range to enter the data (adjust it as required).

Right click on the worksheet tab that should have this behavior and select view
code. Paste this into the code window (usually on the right).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[theSizz]

Thanks Dave that code will solve the problem.
Thanks for the help.

theSizz