Adding zero as prefix Cells with Zeros

[vletm]

hmm...
Do anyone else wonder,
why those sample files are good to get?
That B-column's all original values are not numbers ... text maybe ...
and someway should add "00000"...

 

[jindon]

we have CIN numbers which are 10 digit numbers prefix and downloaded data comes with only 5 digit need to add prefix 00000 zeros in "B" column and not sure if this possible with excel formula or vba, please suggest.

Try

Sub test()
    With Range("b1", Range("b" & Rows.Count).End(xlUp))
        .Value = Evaluate("if(" & .Address & "<>"""",if(isnumber(" & .Address & "),text(" & .Address & ",""0000000000"")," & .Address & "))")
    End With
End Sub