How to Replace Nos {1,2,3,4,5,6,7,8,9} with some text appended to the number and a comma?

As you may have noted the UDF is not mine:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
.
.
.

so I won't take credit... but it is a popular piece of code used for this purpose.

This part ROW(INDIRECT("1:"&LEN(A1))) when part of an array formula, simply creates an array from 1 to the length of the string in A1, so that exactly the number of characters found in A1 are checked in the MID function one at a time.