Numbers digits

[Panoos64]

Hi to all of you,
I would like to use a VBA code so that, to arrange the numbers in column “C” up to 7 digits and placed the letter “I” and “-“ at left side. Therefore that in case that, the number doesn't contains 7 digits should place "0" so that to complete it. I present below an extract of original data and expected result. Thank you all in advance.

Original data

Expected result

 

[Rick Rothstein]

Give this macro a try...

Sub InvoiceNumbers()
  With Range("C2", Cells(Rows.Count, "C").End(xlUp))
    .Value = Evaluate("TEXT(" & .Address & ",""I-0000000"")")
  End With
End Sub

 

[Panoos64]

Give this macro a try...

Sub InvoiceNumbers()
  With Range("C2", Cells(Rows.Count, "C").End(xlUp))
    .Value = Evaluate("TEXT(" & .Address & ",""I-0000000"")")
  End With
End Sub

Hi Rick,
It works but it appears all numbers the same. Should arrange the numbers like "I-0005569" , "I-0045674" e.t.c. It gets the first number from row 2 and appears it to all below rows. However thank you for your support and attempt to resolve my issue. Hv a great day

 

[Rick Rothstein]

Hi Rick,
It works but it appears all numbers the same.

That is not how it works for me. When I run the code I posted, it produces exactly the same list that you showed for your expected result.

 

[navic]

Therefore that in case that, the number doesn't contains 7 digits should place "0" so that to complete it

Youn can to do without VBA
Try i 'D' column then Copy/Paste as Values in 'C' column

=IF(LEN(C2)=1,"I-"&"000000"&C2,IF(LEN(C2)=2,"I-"&"00000"&C2,IF(LEN(C2)=3,"I-"&"0000"&C2,IF(LEN(C2)=4,"I-"&"000"&C2,IF(LEN(C2)=5,"I-"&"00"&C2,IF(LEN(C2)=6,"I-"&"0"&C2,IF(LEN(C2)=7,"I-"&C2,"")))))))

 

[mohadin]

Then try

Sub InvoiceNumbers()
    a = Range("C2", Cells(Rows.Count, "C").End(xlUp))
    For i = 1 To UBound(a)
        a(i, 1) = "I-" & WorksheetFunction.Rept("0", 7 - Len(a(i, 1))) & a(i, 1)
    Next
    Range("C2", Cells(Rows.Count, "C").End(xlUp)) = a
End Sub

and formula

=IF(C2<>"","I-"&REPT("0",7-LEN(C2))&C2,"")

 

[dmt32]

Hi,
try formatting the Column & see if this will give required result

Assuming values in Column are as shown, just numbers then with sheet active, run this code once

Sub FormatColumn()
    Columns(3).NumberFormat = """I-""0000000"
End Sub

You should see your cells formatted in required manner but the underlying cell value will just contain a number.
The format should now apply to any numeric values added in the column.

Dave

 

[Panoos64]

Then try

Sub InvoiceNumbers()
    a = Range("C2", Cells(Rows.Count, "C").End(xlUp))
    For i = 1 To UBound(a)
        a(i, 1) = "I-" & WorksheetFunction.Rept("0", 7 - Len(a(i, 1))) & a(i, 1)
    Next
    Range("C2", Cells(Rows.Count, "C").End(xlUp)) = a
End Sub

and formula

=IF(C2<>"","I-"&REPT("0",7-LEN(C2))&C2,"")

Hi Mohadin, it works perfect and nicely. Thank you for your time spent for my project. Hv a great day

 

[Panoos64]

Hi dmt, Yes it works perfect and is an easy code. Thank you for your support. Hv a lovely day

 

[Panoos64]

Mohadin, your formula works too. Thank once again