http://www.vba-market.com/FormatAcctNumber.xlsm

Formula Solution:

Quick and easy formula to add leading zeros to account number.  Works by appending zeros to the front of the number then taking only the right 9 digits.

Excel: =Right(“000000000” & C2, 9)

Access:  Right(“000000000” & [Tbl_All_Accounts]![AccountNumber],9)

VBA solution:

InputBox is used to get the number of digits the account number should be formatted to.  For loop is used to add the correct number of zero’s to the number.

Sub FormatAcctNumber()
Dim acctNum, myValue As Variant
Dim lenVal, rws, i, j As Long

myValue = InputBox("Enter number of digits for Account Number")
rws = WorksheetFunction.CountA(Columns(1))

    For i = 2 To rws
        acctNum = Cells(i, 1).Value
        lenVal = Len(Cells(i, 1))
        For j = lenVal To myValue - 1
            acctNum = "0" & acctNum
        Next j
        Cells(i, 1).Value = "'" & acctNum
    Next i

ReDim acctList(1 To rws)
    For i = 1 To rws
        acctList(i) = Cells(i, 1).Value
    Next i

MsgBox Join(acctList, Chr(13))
End Sub