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.

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.

### Syntax

 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
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

 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