Option Explicit
Option base 1

Function DigitLengthStandardiser(InCell As String, ReqDigitLen As Integer, Optional SpacingChar As String = "0") As String
    'DigitLengthStandardiser adds spacing characters (ie "0", "-" etc) in front of a sequence of digits contained within a string.
    '   For example, the string 'Team 1' can be converted to 'Team 001'.
    '   This is intended to allow correct column sorting when alphabetic sorting is required.
    
    'Parameters are:
    '   InCell, the input string to be acted upon
    '   ReqDigitLen is the length you want the sequence to be. Spacing characters are added to the front of the original sequence of digits.
    '   SpacingChar is the desired spacing character (defaults to '0'). Normally '0' will work best but any character can be used
    
    Dim Ind As Integer, StrLen As Integer, StartPos As Integer, EndPos As Integer, CurrDigitLen As Integer, CharSpaceReq As Integer
    Dim StrArray() As String
    Dim RetStr As String, SpacingStr As String
    Dim HasDigits As Boolean
    
    'Determine the length of the original string
    StrLen = Len(InCell)
    
    'For empty cells, exit directly.
    'If defined spacing character is "", also exit directly as no changes should be made
    If StrLen = 0 Or SpacingChar = "" Then
        DigitLengthStandardiser = InCell
        Exit Function
    End If
    
    'Three rows, and enough columns for each character in string
    '   1st row contains the string as an array of characters
    '   2nd row contains the character code for each character.
    '   3rd row uses T or F to indicate if it is a numeric character
    ReDim StrArray(3, StrLen)
    
    'Variable to record if digits exist in string. If not, return original string unchanged
    HasDigits = False
    
    For Ind = 1 To StrLen
        'This multidimensional array can be compressed into a single row but it is left as is for testing purposes
        StrArray(1, Ind) = Mid(InCell, Ind, 1)
        StrArray(2, Ind) = Asc(StrArray(1, Ind))
        'Test each character to see if it is numeric
        If StrArray(2, Ind) > 47 And StrArray(2, Ind)  1 Then
            RetStr = Mid(InCell, 1, StartPos - 1)
        End If
        
        'If numeric sequence is larger than that required, no spacing characters should be added
        If CharSpaceReq > 0 Then
            SpacingStr = String(CharSpaceReq, SpacingChar)
        Else
            SpacingStr = ""
        End If
        
        RetStr = RetStr & SpacingStr
        RetStr = RetStr & Mid(InCell, StartPos, Len(InCell))
    End If
    
    DigitLengthStandardiser = RetStr
    
End Function