VB: Add Zero

[harky]

I had this vb, it will able to add zero in-front .
But is it possible to add in 0 @ column C16 which number is in 4 digital number?
If not wrong, this code will change all the format to TEXT but i only want to change to TEXT of 4 digital number.


The rest which start from 1xxxx should remain as GENERAL format.
Since excel are not able to put 0 if it is not in text format.

Sub Add_Zeros()    Dim a
    Dim i As Long
    
    With Range("C16", Range("C" & Rows.Count).End(xlUp))
       .NumberFormat = "@"
       a = .Value
       For i = 1 To UBound(a, 1)
        If Len(a(i, 1)) Then
          a(i, 1) = Right("00000" & a(i, 1), 5)
        End If
       Next i
       .Value = a
    End With
End Sub

 

[vds1]

Hello,

Will this work for you?

Sub Add_Zeros()
     Range("C16", Range("C" & Rows.Count).End(xlUp)).NumberFormat = "00000"

End Sub

 

[harky]

the logic is correct but i want to add a 0 on excel.

on excel it should show
01111 rather than 1111

Hello,

Will this work for you?

Sub Add_Zeros()
     Range("C16", Range("C" & Rows.Count).End(xlUp)).NumberFormat = "00000"

End Sub

 

[vds1]

Number format will be used to display the number on excel cell. If you still want to show "0" on function/Formula bar then your code will work. If you are then doing calculations using column "c" you should first perform basic operations like multiply by 1 then it would convert it to number.

 

[harky]

yes the code i put will works but issue is, it changed all to TEXT format.
I only want to change 0xxxx - 09999 to TEXT format while the rest, start from 1xxxx remind as Number format.

Number format will be used to display the number on excel cell. If you still want to show "0" on function/Formula bar then your code will work. If you are then doing calculations using column "c" you should first perform basic operations like multiply by 1 then it would convert it to number.

 

[harky]

I have the following data as text in my file:

5
13
108
5525
97875

I would like the following results:
00005 (TEXT Format)
00013 (TEXT Format)
00108 (TEXT Format)
05525 (TEXT Format)
97875 (GENERAL / NUMBER Format)

 

[rlv01]

Sub Add_Zeros()
    Dim a
    Dim i As Long
    Dim R As Range

    For Each R In Range("C16", Range("C" & Rows.Count).End(xlUp))
        If Len(R.Text) >= 5 And Left(R.Text, 1) <> "0" Then
            R.NumberFormat = "General"
        Else
            R.NumberFormat = "@"
            R.HorizontalAlignment = xlRight
            R.Value = Right("00000" & R.Text, 5)
        End If
    Next R
End Sub

 

[harky]

yes! this is what i needed. thanks u. But i change xlRight to xlCenter (after google search )

Sub Add_Zeros()
    Dim a
    Dim i As Long
    Dim R As Range

    For Each R In Range("C16", Range("C" & Rows.Count).End(xlUp))
        If Len(R.Text) >= 5 And Left(R.Text, 1) <> "0" Then
            R.NumberFormat = "General"
        Else
            R.NumberFormat = "@"
            R.HorizontalAlignment = [COLOR=#ff0000]xlCenter[/COLOR]
            R.Value = Right("00000" & R.Text, 5)
        End If
    Next R
End Sub