Option Explicit 
Sub PadCells() 
    On Error Goto Error_Handler 
    Dim Cll As Excel.Range 
    Dim Data As Excel.Range 
    Dim TargetLength As Long 
    Dim CellLen As Long 
    Dim CellValue As String 
    Dim Truncate As VBA.VbMsgBoxResult 
    Const ForceText As String = "'" 
    Const Zero As String = "0" 
    Const LengthError As Long = 5 
    Set Data = Excel.Selection 
    TargetLength = Excel.Application.InputBox( _ 
    "Enter a numeric value indicated the desired length of data in characters:", _ 
    "Enter Target Cell Length", VBA.Len(Data.Cells(1, 1).Value), Type:=1) 
    If TargetLength = 0 Then 'Detect Cancel
        MsgBox "Operation Aborted", vbInformation + vbMsgBoxSetForeground 
        Exit Sub 
    End If 
    For Each Cll In Data.Cells 
        If Not Cll.HasFormula Then 
            CellValue = Cll.Value 
            CellLen = VBA.Len(CellValue) 
            Cll.Value = ForceText & _ 
            VBA.String$(TargetLength - CellLen, Zero) & CellValue 
        End If 
    Next Cll 
    Exit Sub 
Error_Handler: 
    If VBA.Err.Number = LengthError Then 
        Truncate = VBA.MsgBox("Encountered cell at " & Cll.Address & _ 
        " with length of " & CellLen & _ 
        ", do you wish to truncate it's value to " & TargetLength & _ 
        " characters? (If you select the new value will be """ & _ 
        VBA.Right$(CellValue, TargetLength) & """.)", _ 
        vbQuestion + vbYesNoCancel + vbMsgBoxSetForeground, "Invalid Length") 
        If Truncate = vbCancel Then 
            MsgBox "Operation Aborted", vbInformation + vbMsgBoxSetForeground 
            Exit Sub 
        ElseIf Truncate = vbYes Then 
            Cll.Value = ForceText & VBA.Right$(CellValue, TargetLength) 
        End If 
        Resume Next 
    End If 
    VBA.MsgBox VBA.Err.Number, vbCritical + vbMsgBoxSetForeground + _ 
    vbMsgBoxHelpButton, "Error: " & VBA.Err.Number, VBA.Err.HelpFile, _ 
    VBA.Err.HelpContext 
End Sub 
 
Sub PadSegments() 
    On Error Goto Error_Handler 
    Dim Cll As Excel.Range 
    Dim Data As Excel.Range 
    Dim TargetLength As Long 
    Dim SegmentLen As Long 
    Dim Truncate As VBA.VbMsgBoxResult 
    Dim Delimiter As String 
    Dim TmpArray As Variant 
    Dim SegmentIndex As Long 
    Const ForceText As String = "'" 
    Const Zero As String = "0" 
    Const LengthError As Long = 5 
    Set Data = Excel.Selection 
    TargetLength = Excel.Application.InputBox( _ 
    "Enter a numeric value indicated the desired length of data in characters:", _ 
    "Enter Target Cell Length", VBA.Len(Data.Cells(1, 1).Value), Type:=1) 
    If TargetLength = 0 Then 'Detect Cancel
        MsgBox "Operation Aborted", vbInformation + vbMsgBoxSetForeground 
        Exit Sub 
    End If 
    Delimiter = VBA.InputBox("Enter the delimter to pad to:", "Enter Delimiter", "-") 
    If Delimiter = vbNullString Then 'Detect Cancel
        MsgBox "Operation Aborted", vbInformation + vbMsgBoxSetForeground 
        Exit Sub 
    End If 
    For Each Cll In Data.Cells 
        If Not Cll.HasFormula Then 
            TmpArray = VBA.Split(Cll.Value, Delimiter) 
            For SegmentIndex = Zero To UBound(TmpArray) 
                SegmentLen = VBA.Len(TmpArray(SegmentIndex)) 
                TmpArray(SegmentIndex) = VBA.String$(TargetLength - SegmentLen, Zero) & TmpArray(SegmentIndex) 
            Next 
            Cll.Value = ForceText & VBA.Join(TmpArray, Delimiter) 
        End If 
    Next Cll 
    Exit Sub 
Error_Handler: 
    If VBA.Err.Number = LengthError Then 
        Truncate = VBA.MsgBox("Encountered cell at " & Cll.Address & _ 
        " with length of " & SegmentLen & _ 
        ", do you wish to truncate it's value to " & TargetLength & _ 
        " characters? (If you select the new value will be """ & _ 
        VBA.Right$(TmpArray(SegmentIndex), TargetLength) & """.)", _ 
        vbQuestion + vbYesNoCancel + vbMsgBoxSetForeground, "Invalid Length") 
        If Truncate = vbCancel Then 
            MsgBox "Operation Aborted", vbInformation + vbMsgBoxSetForeground 
            Exit Sub 
        ElseIf Truncate = vbYes Then 
            TmpArray(SegmentIndex) = VBA.Right$(TmpArray(SegmentIndex), TargetLength) 
        End If 
        Resume Next 
    End If 
    VBA.MsgBox VBA.Err.Number, vbCritical + vbMsgBoxSetForeground + _ 
    vbMsgBoxHelpButton, "Error: " & VBA.Err.Number, VBA.Err.HelpFile, _ 
    VBA.Err.HelpContext 
End Sub