Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Retain Leading Zero in Custom Format

  1. #1
    Join Date
    1st June 2005
    Posts
    211

    Retain Leading Zero in Custom Format

    Hello people

    I have a number format: 000-000-00

    This format is a eight lenght code like : 123-456-78

    But i have a problem sometimes when i enter a code that begins with zero it show it on the format but not on actual code the zero disappears.

    This there a way i can keep the zeros from disappearing from the code like this: 00012345 not 12345.

    Thanks

    Mini12

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    13th September 2003
    Location
    Singapore
    Posts
    1,236

    Re: Retain Leading Zero in Custom Format

    hi Mini,

    try custom format the number to : 00000000

    hth
    Last edited by Dave Hawley; November 24th, 2006 at 14:23.
    Cheers
    ___________
    Xlite
    All you need to learn VBA is an internet connection and Ozgrid.com

  3. #3
    Join Date
    25th May 2005
    Location
    St. Clair Shores, MI, USA
    Posts
    150

    Re: Retain Leading Zero in Custom Format

    xlite, Mini's custom number format is already set to 000-000-00 for display in the cell. As I've found out in another thread, its hard to return the format information in VB code for a custom format such as this.

    Enter the following values in the Range A1:A8 on a sheet to test the two types of VB codes shown below.
    1
    12
    123
    1234
    12345
    123456
    1234567
    12345678

    VB CODE1: Using the .text qualifier to grab the cell contents and retain its custom format.

    VB:
    Sub ReadCustomString() 
         
         'Reads in values in range A1:A8
        For Row = 1 To 8 
            mycode = Cells(Row, 1).Text '.text keeps custom format
            MsgBox mycode 
        Next 
         
    End Sub 
    
    
    VB CODE2: Using the .value qualier to grab the unformatted number, then reapply the custom format 000-000-00. This might be the safest bet if you want to make sure you can compare your mycode variable to something later in your code, though it is a bit lenghty. If you should change your format on the sheet, it will not change the custom format applied by this code.

    VB:
    Sub WriteCustomString() 
         
         'Reads in values in range A1:A8
        For Row = 1 To 8 
            mycode = Cells(Row, 1).Value '.value does not keep custom format
            mycode = CStr(mycode) 'converts value to string
             
            Select Case Len(mycode) 'gets length of string
                 
                 'applies a custom format: 000-000-00 to the string
            Case 1 '000-000-01
                block1 = Mid(mycode, 1, 1) 
                mycode = "000-000-0" & block1 
            Case 2 '000-000-12
                block1 = Mid(mycode, 1, 2) 
                mycode = "000-000-" & block1 
            Case 3 '000-001-23
                block1 = Mid(mycode, 1, 1) 
                block2 = Mid(mycode, 2, 2) 
                mycode = "000-00" & block1 & "-" & block2 
            Case 4 '000-012-34
                block1 = Mid(mycode, 1, 2) 
                block2 = Mid(mycode, 3, 2) 
                mycode = "000-0" & block1 & "-" & block2 
            Case 5 '000-123-45
                block1 = Mid(mycode, 1, 3) 
                block2 = Mid(mycode, 4, 2) 
                mycode = "000-" & block1 & "-" & block2 
            Case 6 '001-234-56
                block1 = Mid(mycode, 1, 1) 
                block2 = Mid(mycode, 2, 3) 
                block3 = Mid(mycode, 5, 2) 
                mycode = "00" & block1 & "-" & block2 & "-" & block3 
            Case 7 '012-345-67
                block1 = Mid(mycode, 1, 2) 
                block2 = Mid(mycode, 3, 3) 
                block3 = Mid(mycode, 6, 2) 
                mycode = "0" & block1 & "-" & block2 & "-" & block3 
            Case 8 '123-456-78
                block1 = Mid(mycode, 1, 3) 
                block2 = Mid(mycode, 4, 3) 
                block3 = Mid(mycode, 7, 2) 
                mycode = block1 & "-" & block2 & "-" & block3 
            End Select 
            MsgBox mycode 
        Next 
         
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Retain Leading Zero in Custom Format

    Hi,

    VB:
    Private Sub Worksheet_Change(ByVal Target As Range) 
        If Target.Column <> 1 Then Exit Sub 
        If Target.Cells.Count > 1 Then Exit Sub 
        If Len(Target) > 8 Then Exit Sub 
        Dim x   As Byte 
        x = Len(Target) 
        Application.EnableEvents = False 
        Target = Application.WorksheetFunction.Text(Application.WorksheetFunction.Rept(0, _ 
        8 - x) & Target.Value, "000-000-00") 
        Application.EnableEvents = True 
    End Sub 
    
    
    HTH

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Retain Leading Zeros In Decimals
    By richadj4 in forum Excel General
    Replies: 1
    Last Post: April 15th, 2008, 14:38
  2. Retain Leading Apostrophe in Cell
    By Michael Hayes in forum Excel General
    Replies: 4
    Last Post: September 8th, 2007, 14:02
  3. Retain Leading Zeros
    By gwr345 in forum Excel General
    Replies: 9
    Last Post: December 30th, 2006, 11:34
  4. Retain leading zeros in numbers
    By chshiba in forum Excel General
    Replies: 3
    Last Post: August 4th, 2006, 16:29
  5. Retain leading zero in Numbers
    By legaltrends in forum Excel General
    Replies: 7
    Last Post: August 31st, 2005, 15:38

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno