Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom cell formats
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom cell formats
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom cell formats
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 , 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Reference Cell in custom format???? | Excel Discussion (Misc queries) | |||
Changing Cell formats to date fields automatically | Excel Worksheet Functions | |||
How do I format a cell for a custom part number? | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |