add zeros in front to meet string number of 5

Question

I have values of 120, 136, 7120, 72136. The maximum string length should be 5, how do I go about making 120 "00120" and 136 "00136" etc?

Answer 1

Single line will work

• VBA

Range("A1").Value = "'" & Format(Range("A1").Value, "00000")

• Excel

=TEXT(A1,"00000")

Answer 2

In your simple case you can try something simple like this:

Sub FiveCharString()

    Dim myStr As String

    myStr = "136"

    If Len(myStr) = 2 Then
        myStr = "000" & myStr
    ElseIf Len(myStr) = 3 Then
        myStr = "00" & myStr
    ElseIf Len(myStr) = 4 Then
        myStr = "0" & myStr
    End If

    Debug.Print myStr

End Sub

Returns 00136.

Answer 3

Function FillWithZero(number as long, digitCount as long) as string
     FillWithZero = Right(String(digitCount , "0") & number , digitCount)
End Function

Answer 4

Use a custom number format in your cells.
See Using a custom number format to display leading zeros
or Keeping leading zeros and large numbers

Or .NumberFormat = "00000" on your range.

I don't recommend to convert it into a string (unless it is something like a serial number that is not treated as an actual number).

Answer 5

A simpler version compared to Dean's

Sub StrLength()
Dim i As Long, str As String

str = "136"
i = Len(str)

StrLength = String(ExpectedLength - Len(str), "0") & str

End Sub

Small subroutines as these can easily be used as Functions, where you call the function in a regular sub. For example, when you are looping through a range of cells:

Function StrLength(str As String, ExpectedLength As Long) As String
Dim i As Long

i = Len(str)

StrLength = String(ExpectedLength - Len(str), "0") & str

End Function


Sub Test()
Dim c As Range

For each c In ThisWorkbook.Sheets(1).Range("A1:B200")
    If Len(c.Value) < 5 Then c.Value = StrLength(Str:=c.Value, ExpectedLength:=5)
Next c

End Sub