' Reformats a list from a simple delimitation to a numbered list ' Accepts arrays of strings for inList (allowing array formulas) ' numFormat is a standard Excel-style format string (default "0. ") ' inDelimiter is the delimiter in the input list ' outDelimiter is the delimiter for the output list Public Function TO_NUMBERED_LIST(inList As Variant, Optional numFormat As Variant, _ Optional inDelimiter As Variant, Optional outDelimiter As Variant) As Variant Dim i As Integer, j As Integer ' Set default parameters If IsMissing(numFormat) Then numFormat = "0). " If IsMissing(inDelimiter) Then inDelimiter = vbNewLine If IsMissing(outDelimiter) Then outDelimiter = inDelimiter If IsArray(inList) Then ' Must loop through each entry if using as an array formula Dim outList() As Variant ReDim outList(0 To (UBound(inList) - LBound(inList)), 1 To 1) j = 0 For i = LBound(inList) To UBound(inList) If IsError(inList(i, 1)) Then outList(j, 1) = inList(i, 1) Else outList(j, 1) = MakeNumbered(CStr(inList(i, 1)), CStr(numFormat), CStr(inDelimiter), CStr(outDelimiter)) End If j = j + 1 Next TO_NUMBERED_LIST = outList Else TO_NUMBERED_LIST = MakeNumbered(CStr(inList), CStr(numFormat), CStr(inDelimiter), CStr(outDelimiter)) End If End Function ' Helper function to do the actual work of splitting lists, numbering them, and recombining them Private Function MakeNumbered(inList As String, Optional numFormat As String, _ Optional inDelimiter As String, Optional outDelimiter As String) As String Dim i As Integer Dim tokenArr() As String tokenArr = Split(inList, inDelimiter) For i = 0 To UBound(tokenArr) tokenArr(i) = Format(i + 1, numFormat) & tokenArr(i) Next MakeNumbered = Join(tokenArr, outDelimiter) End Function
