Format number of digits in account number
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 leading 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
