Keep numeric ids the same length

Brief description:

Adds spacing characters in front of numeric ids so that their length is the same. ie 10 & 200 could be converted to 00010 & 00200. This allows for correct alpha-numeric sorting. Instead of data being sorted as [Team 1, Team 11, Team 2, Team 3], it will be sorted as [Team 001, Team 002, Team 003, Team 011]

Full description:

This macro inserts spacing characters before the numeric id to ease making the numeric sequence a uniform length.

Purpose: It is not uncommon for data records to be given numeric ids without due regard to ensuring the ids are the same length. Hence, instead of data being sorted as desired (ie Team 1, Team 2, Team 3, Team 11]), it is likely to be sorted as: [Team 1, Team 11, Team 2, Team 3].

However, using ‘DigitLengthStandardiser’ with a specified required digit length of 3, results will be sorted as [Team 001, Team 002, Team 003, Team 011]. Any ids starting with 1 will be listed before ids starting with 2, even when the actual number is larger.

Example input and output:

Desired spacing character Required Int size Test row description Test record Result
0 5 Empty Cell
0 5 String, no digits abc abc
0 5 Integer, smaller than required 123 00123
0 5 Integer, same as required 12345 12345
0 5 Integer, larger than required 123456 123456
0 5 Integer, start of character string 123Hero 00123Hero
0 5 Integer, middle of character string Hero123Hero Hero00123Hero
0 5 Integer, end of character string Hero123 Hero00123
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)
            SpacingStr = ""
        End If
        RetStr = RetStr & SpacingStr
        RetStr = RetStr & Mid(InCell, StartPos, Len(InCell))
    End If
    DigitLengthStandardiser = RetStr
End Function