Access Excel add leading zero

This Access / Excel tutorial explains how to add leading zero using Text Function or using custom Function to pad other text in suffix or prefix.

Excel add leading zero

Number in Excel can come in two forms – Text or Number.

For number in Number format, you may not add zero in prefix

For number in Text format, you can add a zero in prefix

In order to convert a Number to Text, add a ‘ in the prefix. For example, instead of typing 00123, type ‘00123

Sometimes you receive data originally in Text format such as 00123 but the formatting is lost and it becomes Number format 123. Assume that you know how many digits the text originally has, say 8 digits, then you can make use of Text Function.

=Text(A1, “00000000”)

Now you can see the leading zero makes up to 8 digits.

Access Excel VBA custom Function to add text in prefix or suffix

I created two custom Functions to simulate Oracle PL/SQL Functions LPad (left pad) and RPad (right pad), which are used to add any text in prefix or suffix up to specific number of characters.

This custom Function can be used in Access and Excel.

Left Pad (add leading zero in prefix)

Syntax

wLpad(sInput, digits, padString)

sInput	The text to be manipulated
digits	Number of characters to add in prefix
padString	The text to be added to sInput, must be one character

VBA Code

Public Function wLpad(sInput As String, digits As Integer, padString As String) As String
    sInputLen = Len(sInput)
    If digits <= sInputLen Then
        result = sInput
    Else
        For i = 1 To (digits - sInputLen)
            result = result & padString
        Next i
        result = result & sInput
    End If
    wLpad = result
End Function

Example

Number	Formula	Result
1	=wLpad(A2,8,0)	00000001
12	=wLpad(A3,8,0)	00000012
123	=wLpad(A4,8,0)	00000123
1234	=wLpad(A5,8,0)	00001234
12345	=wLpad(A6,8,0)	00012345
123456	=wLpad(A7,8,0)	00123456
1234567	=wLpad(A8,8,0)	01234567
12345678	=wLpad(A9,8,0)	12345678
123456789	=wLpad(A10,8,0)	123456789

Right Pad (add zero in suffix)

Syntax

wRpad(sInput, digits, padString)

sInput	The text to be manipulated
digits	Number of characters to add in suffix
padString	The text to be added to sInput, must be one character

VBA Code

Public Function wRpad(sInput As String, digits As Integer, padString As String) As String
    sInputLen = Len(sInput)
    If digits <= sInputLen Then
        result = sInput
    Else
        For i = 1 To (digits - sInputLen)
            result = result & padString
        Next i
        result = sInput & result
    End If
    wRpad = result
End Function

Example

Number	Formula	Result
1	=wRpad(A2,8,0)	10000000
12	=wRpad(A3,8,0)	12000000
123	=wRpad(A4,8,0)	12300000
1234	=wRpad(A5,8,0)	12340000
12345	=wRpad(A6,8,0)	12345000
123456	=wRpad(A7,8,0)	12345600
1234567	=wRpad(A8,8,0)	12345670
12345678	=wRpad(A9,8,0)	12345678
123456789	=wRpad(A10,8,0)	123456789

 

 

Access Excel add leading zero