VBA Convert Integer To String With Leading Zeros. when dealing with large data bases we commonly see numbers without leading zeros. But when we add in Excel sheet it doesn’t allow leading zeros. So we are converting to string by adding single quote. After that we can add zeros. It doesn’t show single quote in the Excel Sheet.
Let us see an example how to convert an integer value to string with leading zeros. We can
'VBA Convert Integer To String With Leading Zeros Sub VBAF1_Convert_Integer_To_String_With_Leading_Zeros() 'Variable declaration Dim iValue As Integer, sResult As String 'Assign value to a variable iValue = 484 'Convert Integer To String by Leading with Zero sResult = "0" & CStr(iValue) 'Display on the screen MsgBox sResult, vbInformation, "VBAF1" 'Update in the Excel Sheet Sheets("Sheet2").Range("D26") = "'0" & CStr(iValue) End Sub
Explanation: You can notice there is a difference between displaying on the screen and displaying on the worksheet. Which we displayed on the screen can’t display on the screen with leading zero. We need to add single quote as well.
You can also find following alternative methods.
1st Case iResult = CStr(Format(484, "0000")) Output: 0484 In the above method we have added 4 zero's to format. Here the value length is 3 and added one more leading zero to string. You can also add as many number of zeros. Let us see another example.. iResult = CStr(Format(484, "000000")) Output: 000484 You can observe difference between 2 examples. 2nd Case iValue=844 Sheets("Sheet2").Range("A1") = Chr(39) & "0" & iValue Output: '0844
You can see output in Sheet 2 at Range A1.
You can refer the following link for the step by step instructions.
Click on the following links of the useful resources. These helps to learn and gain more knowledge.