Convert selection to Leading zero format

Question

I am trying to build a simple macro that converts a selected range with numeric values to a "0000" format (e.g. 50,75,888, 1000 would be 0050,0075, 0888, 1000) i.e. it picks up each value in each cell and and returns a string value back to the sheet which can then be manipulated in Excel

Almost there (I think..) I just need help with the the $format function

Sub LeadingZero()
Dim RngSelected As Range
Dim R As String
Dim RCell As Range
Dim Rrng As Range
Dim RevNum As Long

On Error Resume Next
Set RngSelected = Application.InputBox("Please select a range of cells you want to convert to 0000 format", _
                                          "SelectRng", Selection.Address, , , , , 8)

R = RngSelected.Address
Set Rrng = Range(R)

For Each RCell In Rrng.Cells
    RCell.Value = Format$(RCell, "0000")    'this is the line I want to work!
    'RCell.Value2 = Format$(RCell, "0000")  doesn't seem to work either
    Next RCell
End Sub

Thanks

Answer 1

Did you specifically want to do it with formatting? If you actually want to convert the value (useful for lookup and the like) then this function will do what you want.

Function FourDigitValues(InputString As String)
Dim X As Long, MyArr As Variant
MyArr = Split(InputString, ",")
For X = LBound(MyArr) To UBound(MyArr)
    MyArr(X) = Right("0000" & MyArr(X), 4)
Next
FourDigitValues = Join(MyArr, ",")
End Function