How to add 0 before number

Help Activities
, ,
1

Hi Team,

I am checking number with 7 digit in excel and if the 7 digit appears adding 0 before the number using below VBA code. After the run I can see 0 adding for 7 digit number but if click on cell to copy the number 0 is not appearing in text bar. Could you please provide the solution using classic activities/vba code.
VBA COde:
Function ChangeColumnFormat(sheetName As String, columnLetter As String)
ActiveWorkbook.Sheets(sheetName).Activate
Columns(columnLetter & “:” & columnLetter).Select
Selection.NumberFormat = “00000000”
ActiveWorkbook.Save
End Function

Excel:

image
image281×193 20 KB

TIA

2

https://www.indeed.com/career-advice/career-development/leading-zeros-in-excel

3

@Manaswini_UI,

Use this VBA code(Change the Sheet name as per your data):

Sub ConvertTo8Digit()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name

    ' Insert a new column at B
    ws.Columns("B").Insert Shift:=xlToRight

    ' Loop through each cell in Column A and convert to 8-digit number
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Dim i As Long
    For i = 1 To lastRow
        ws.Cells(i, 2).Value = Format(ws.Cells(i, 1).Value, "00000000")
    Next i

    ' Copy the values from Column B to Column A
    ws.Range("B1:B" & lastRow).Copy
    ws.Range("A1").PasteSpecial Paste:=xlPasteValues

    ' Delete the temporary Column B
    ws.Columns("B").Delete

    ' Clean up
    Application.CutCopyMode = False
End Sub
4

no change in file after running this vbcode

5

Could you please check and provide solution

6

@Manaswini_UI

Please try this…for excel to retain leading zero it needs to treat number as text so we add a single literal at start

Function ChangeColumnFormat(sheetName As String, columnLetter As String)
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets(sheetName)
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, columnLetter).End(xlUp).Row
    Dim cell As Range
    For Each cell In ws.Range(columnLetter & "1:" & columnLetter & lastRow)
        If IsNumeric(cell.Value) And Len(Trim(cell.Value)) > 0 Then
            cell.Value = "'" & Right("00000000" & Trim(cell.Value), 8)
        End If
    Next cell
End Function

cheers

1 Like