• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding zero as prefix Cells with Zeros

Hello

I get lot of data from different systems and need to clean up and real challenge comere here, 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.

Expected result, which i was doing manually.

upload_2017-2-20_1-10-53.png

Really appreciate for your quick response.
Cheers
 
Dear vletm.

i work in an international organisation where data cannot be disclosed, hope you can understand and that is test screenshot from my lapy which just realised wrong thought of giving a output screenshot. Thank you.

@ age of 50 trying my best to help my organisation with basic level of skills.

Cheers!
 
Last edited:
May be this.
It works wherever data with selection.
Suggested to use in personel workbook.

Code:
Sub Prefix()
    Dim MyRange As Range
    Dim MyCell As Range
   
    Set MyRange = Selection
   
    For Each MyCell In MyRange
   
    If Not IsEmpty(MyCell) Then
        MyCell.NumberFormat = "@"
        MyCell = "0000000000" & MyCell
        MyCell = Right(MyCell, 10)
    End If

  Next MyCell
End Sub
 
May be this.
It works wherever data with selection.
Suggested to use in personel workbook.

Code:
Sub Prefix()
    Dim MyRange As Range
    Dim MyCell As Range
  
    Set MyRange = Selection
  
    For Each MyCell In MyRange
  
    If Not IsEmpty(MyCell) Then
        MyCell.NumberFormat = "@"
        MyCell = "0000000000" & MyCell
        MyCell = Right(MyCell, 10)
    End If

  Next MyCell
End Sub

Sure enough will use personal workbook, been using so that i can use it in any workbook read in our forum.
 
I like to using formulas in VBA with powerful evaluate fn.

Code:
Sub Prefix2()
Dim MyRange As Range, f As String, v As Variant, r As Range, s As String

Set MyRange = Selection
s = MyRange.Address

f = "=TRANSPOSE(IF(LEN(" & s & ")>0,ADDRESS(ROW(" & s & "),1)))"
v = Filter(Evaluate(f), False, False)

If UBound(v) = -1 Then Exit Sub

Set r = Range(Join(v, ","))
r.NumberFormat = "@"

f = "=TRANSPOSE(IF(LEN(" & s & ")>0,RIGHT(REPT(0,10)&" & s & ",10)))"
v = Filter(Evaluate(f), False, False)

r = v

Set MyRange = Nothing
Set r = Nothing

End Sub
 
Last edited:
Hello
There is a problem in this...It will make every cell into 10digits...I think it should change only numbers less then 10 should make it 10...And ignore rest.

RIGHT(X,10) will always take 10 digit from the right barring string length.
 
Deepak.
Will check, Even I got the same requirement at work...But condition is only digits less then or equal to 10...Should be prefixed with zeros to make it 10 Digits....Rest should be untouched with macro.
 
I do not have this issue monty / Deepak as my numbers are fixed 5 digit and need to add 00000 zeros as prefix.

Monty is looking out something different it seems.
 
Hello

Here is the solution.

Code:
Sub Prefix_10()
Dim cel As Range, rg As Range
Dim d As Double
Application.ScreenUpdating = False
Set rg = Range("A2")
Set rg = Range(rg, rg.Worksheet.Cells(Rows.Count, rg.Column).End(xlUp))
rg.NumberFormat = "@"
On Error Resume Next
For Each cel In rg.Cells
    If IsNumeric(cel.Value) Then
        d = Val(cel.Value)
        cel.Value = Format(d, "0000000000")
    End If
Next
On Error GoTo 0
End Sub

upload_2017-2-20_0-38-18.png
 
Another way asking user to select range and perform the action!

Code:
Sub Prefix_10()
Dim cel As Range, rg As Range
Dim d As Double
Dim UserRange As Range
Application.ScreenUpdating = False
Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Range Select", Type:=8)
UserRange.NumberFormat = "@"
On Error Resume Next
For Each cel In UserRange.Cells
    If IsNumeric(cel.Value) Then
        d = Val(cel.Value)
        cel.Value = Format(d, "0000000000")
    End If
Next
On Error GoTo 0
End Sub
 
Trying to handle the error, if the user is not selecting the range then.


Code:
Sub Prefix_10()
Dim cel As Range, rg As Range
Dim d As Double
Dim UserRange As Range
Application.ScreenUpdating = False
On Error GoTo Canceled
Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Range Select", Type:=8)
UserRange.NumberFormat = "@"
On Error Resume Next
For Each cel In UserRange.Cells
    If IsNumeric(cel.Value) Then
        d = Val(cel.Value)
        cel.Value = Format(d, "0000000000")
    End If
Next
Canceled: MsgBox "You have not selected Range"
On Error GoTo 0
End Sub
 
Back
Top