1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by jamesexcel1970, Feb 19, 2017.

  1. jamesexcel1970

    jamesexcel1970 Member

    Messages:
    84
    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
  2. vletm

    vletm Well-Known Member

    Messages:
    1,948
    ... and You show only last five numbers from somewhere?
    As I Tip has written: Upload a Sample File to get a quicker response.
    ... if You're waiting something called 'quick response'!
    Arpanakumar and jamesexcel1970 like this.
  3. jamesexcel1970

    jamesexcel1970 Member

    Messages:
    84
    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: Feb 19, 2017
    Monty likes this.
  4. Monty

    Monty Well-Known Member

    Messages:
    541
    May be this.
    It works wherever data with selection.
    Suggested to use in personel workbook.

    Code (vb):
    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
  5. Deepak

    Deepak Excel Ninja

    Messages:
    2,679
    U can skip one line.

    Code (vb):
          MyCell = Right("0000000000" & MyCell, 10)
  6. jamesexcel1970

    jamesexcel1970 Member

    Messages:
    84
    Sure enough will use personal workbook, been using so that i can use it in any workbook read in our forum.
  7. jamesexcel1970

    jamesexcel1970 Member

    Messages:
    84
    How that will help! for my understanding.
  8. Monty

    Monty Well-Known Member

    Messages:
    541
    I can close my eyes when Deepak says...i can follow blindly, There must be a reason.
    Arpanakumar and jamesexcel1970 like this.
  9. Monty

    Monty Well-Known Member

    Messages:
    541
    Deepak
    I should really learn from u to shorten code..Hats off....It works...Thanks always.
    Arpanakumar and jamesexcel1970 like this.
  10. Deepak

    Deepak Excel Ninja

    Messages:
    2,679
    For formula =RIGHT(REPT(0,10)&B2,10)

    You can also use the same technique in vba too.
  11. jamesexcel1970

    jamesexcel1970 Member

    Messages:
    84
    i can go with you guys..Cheers.
    Am happy for this, quickly grab a beer.
  12. Monty

    Monty Well-Known Member

    Messages:
    541
    Good night Guys...I hate Monday:mad:
    Arpanakumar and jamesexcel1970 like this.
  13. Monty

    Monty Well-Known Member

    Messages:
    541
    Noted.
    Arpanakumar and jamesexcel1970 like this.
  14. Deepak

    Deepak Excel Ninja

    Messages:
    2,679
    I like to using formulas in VBA with powerful evaluate fn.

    Code (vb):

    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: Feb 19, 2017
    Arpanakumar likes this.
  15. Monty

    Monty Well-Known Member

    Messages:
    541
    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.
    Arpanakumar and jamesexcel1970 like this.
  16. Deepak

    Deepak Excel Ninja

    Messages:
    2,679
    RIGHT(X,10) will always take 10 digit from the right barring string length.
  17. Monty

    Monty Well-Known Member

    Messages:
    541
    No luck for me...Can u include in the code you provided.
    Arpanakumar and jamesexcel1970 like this.
  18. Deepak

    Deepak Excel Ninja

    Messages:
    2,679

    My code will not touch the cells those are blank.


    Right fn!!
    upload_2017-2-20_15-22-32.png
  19. Deepak

    Deepak Excel Ninja

    Messages:
    2,679

    Play like as below!!

    f = "=TRANSPOSE(IF((LEN(" & s & ")>0)*(LEN(" & s & ")<10),ADDRESS(ROW(" & s & "),1)))"

    f = "=TRANSPOSE(IF((LEN(" & s & ")>0)*(LEN(" & s & ")<10),RIGHT(REPT(0,10)&" & s & ",10)))"
    jamesexcel1970 and Monty like this.
  20. Monty

    Monty Well-Known Member

    Messages:
    541
    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.
    Arpanakumar and jamesexcel1970 like this.
  21. jamesexcel1970

    jamesexcel1970 Member

    Messages:
    84
    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.
  22. Monty

    Monty Well-Known Member

    Messages:
    541
    Deepak...Any luck.

    Problem is here:

    Set r = Range(Join(f, ","))

    Type mismatch.
    Last edited: Feb 20, 2017
    Arpanakumar likes this.
  23. Monty

    Monty Well-Known Member

    Messages:
    541
    Hello

    Here is the solution.

    Code (vb):
    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
    Arpanakumar likes this.
  24. Monty

    Monty Well-Known Member

    Messages:
    541
    Another way asking user to select range and perform the action!

    Code (vb):
    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
    Arpanakumar likes this.
  25. Monty

    Monty Well-Known Member

    Messages:
    541
    Trying to handle the error, if the user is not selecting the range then.


    Code (vb):
    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
    Thomas Kuriakose and Arpanakumar like this.

Share This Page