Custom cell formats

[iggy10284]

I am trying to create a custom format for my account numbers. An exampl
of one of the account numbers is 9500-01-010-02-010-000-00. I want to b
able to enter just numbers and have the dashes be put in automatically
but here is the kicker.... I also need it to fill spots with zero if n
data is entered. So if I enter 9500 it will appear a
9500-00-000-00-000-000-00. Any help would be greatly appreciated

 

[Guest]

You won't be able to do what you want. Numbers can only be 15 digits long.
No exceptions. I believe you will have to enter the account number as text
and include the dashes. If you had a shorter account number you might be
able to do it, but it would fill in the left with zeros, not the right.

 

[JE McGimpsey]

That's not something that formatting alone can handle.

In addition, since your account numbers are 19 digits long, the entry
cells will have to be preformatted as text or else entries longer than
15 digits will be truncated (or you could prefix them with an
apostrophe), so no number formatting will be applied.


You can put this in the worksheet code module (right-click the worksheet
tab and choose View Code). Change the column to suit:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sZEROS As String = "0000000000000000000"
Const sSEP As String = "-"
Const sNUMFORMAT As String = "@"
Const nTARGETCOL As Long = 1 'A
Dim vDigits As Variant
Dim sInput As String
Dim sOutput As String
Dim nStart As Long
Dim i As Long
With Target
If .Count > 1 Then Exit Sub
If .Column = nTARGETCOL Then
vDigits = Array(4, 2, 3, 2, 3, 3, 2)
sInput = Left(.Text & sZEROS, 19)
nStart = 1
For i = LBound(vDigits) To UBound(vDigits)
sOutput = sOutput & sSEP & _
Mid(sInput, nStart, vDigits(i))
nStart = nStart + vDigits(i)
Next i
On Error Resume Next
Application.EnableEvents = False
.NumberFormat = sNUMFORMAT
.Value = Mid(sOutput, 2)
Application.EnableEvents = True
On Error GoTo 0
End If
End With
End Sub