custom format

[Elsie M]

Hi

This is our problem, we have set up a custom format for credit card number,.
lit this
0000-0000-0000-0000

When we imput the numbers the last digit is always changed to a zero, no
matter what number is input.

ANy help will be greatly appreciated.

EM
FSLF

 

[Don Guillett]

Excel only takes 15 numbers so format as text

 

[Frank Kabel]

Hi
Excel only supports 15 significant digits. So you can't have a number
format with 16 digits. You have to enter this as 'Text'

 

[Don Guillett]

Right click sheet tab>view code>copy/paste this>format col A as TEXT.
Now type in col a 1111222233334444 and you will get your desired result.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'Debra Dalgleish
Dim myStr As String
On Error GoTo errHandler:
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) Then
If Len(Target.Value) < 17 Then
myStr = Right(String(16, "0") & Target.Value, 16)
myStr = Left(myStr, 4) & "-" & Mid(myStr, 5, 4) _
& "-" & Mid(myStr, 9, 4) & "-" & Right(myStr, 4)
Application.EnableEvents = False
Target.Value = myStr
End If
End If
errHandler:
Application.EnableEvents = True
End Sub