Remove leading zeros
To remove the leading zeros in a string we need to apply the Excel VALUE function
Example: Remove leading zeros
|
=VALUE(B5)
|
The formula uses the Excel VALUE function to convert a text string into a number, removing the leading zeros.
|
Sub Remove_leading_zeros()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to remove leading zeors
ws.Range("C5") = Val(ws.Range("B5"))
ws.Range("C5") = Val(ws.Range("B5"))
End Sub
OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named Analysis.
Value: In this example the text value that the VBA code uses to remove the leading zeros from is sourced from cell ("B5"), therefore, if using the exact same VBA code ensure that the text value is captured in the same cell.
Worksheet Name: Have a worksheet named Analysis.
Value: In this example the text value that the VBA code uses to remove the leading zeros from is sourced from cell ("B5"), therefore, if using the exact same VBA code ensure that the text value is captured in the same cell.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code to any cell in the worksheet.
Values Range: Select the text value from which you want to remove the leading zeros by changing the cell reference ("B5") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.