Custom cell formats

[iggy10284]

I am trying to create a custom format for my account numbers. An example of one of the account numbers is 9500-01-010-02-010-000-00. I want to be 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 no data is entered. So if I enter 9500 it will appear as 9500-00-000-00-000-000-00. Any help would be greatly appreciated.

[Sloth]

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.

"iggy10284" wrote:

>
> I am trying to create a custom format for my account numbers. An example
> of one of the account numbers is 9500-01-010-02-010-000-00. I want to be
> 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 no
> data is entered. So if I enter 9500 it will appear as
> 9500-00-000-00-000-000-00. Any help would be greatly appreciated.
>
>
> --
> iggy10284
> ------------------------------------------------------------------------
> iggy10284's Profile: http://www.excelforum.com/member.php...o&userid=32078
> View this thread: http://www.excelforum.com/showthread...hreadid=518291
>
>

[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


In article <[email protected]>,
iggy10284 <[email protected]>
wrote:

> I am trying to create a custom format for my account numbers. An example
> of one of the account numbers is 9500-01-010-02-010-000-00. I want to be
> 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 no
> data is entered. So if I enter 9500 it will appear as
> 9500-00-000-00-000-000-00. Any help would be greatly appreciated.