Adding a comma after every 9 characters

[grussell070769]

I need to add a comma after every nine characters that would yield a very large return.

Example: Current Chain of numbers
144849383600226994968816910897204120078656326273603444077371702

Need:
144849383,600226994,968816910,897204120,078656326,273603444,077371702,

 

[Rick Rothstein]

I only have XL365, so I cannot test this (can't be sure if dynamic arrays are why it works for me or not). In any event, this one-liner function is only for the OP's original question (if it works) and not for a general case (it should handle up to 100 digits)...

Function AddCommas(S As String) As String
  AddCommas = Replace(Application.Trim(Join(Evaluate("MID(""" & S & """,{1,10,19,28,37,46,55,64,73,82,91},9)&"" """))), " ", ",")
End Function

 

[Peter_SSs]

Peter,

I cannot remove the last comma [","], (the one on the right).
Can you help me?

I deliberately made sure that last comma was there because it was there in the "Need" string in post #1.
Yours is a different question but it would just require a small change to the pattern line.

Function AddCommas(s As String, Optional Where As Long = 9, Optional Delim As String = ",") As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(.{" & Where & "})(?!$)"
    AddCommas = .Replace(s, "$1" & Delim)
  End With
End Function

grussell070769.xlsm
	A	B
1	144849383600226994968816910897204120078656326273603444077371702	144849383,600226994,968816910,897204120,078656326,273603444,077371702
Add commas

Cell Formulas
Range	Formula
B1	B1	=AddCommas(A1)

 

[Tom.Jones]

Peter_SSs​

I deliberately made sure that last comma was there because it was there in the "Need" string in post #1.

Yes, I know the OP's request.

Peter_SSs​

Yours is a different question but it would just require a small change to the pattern line.

I know. I tried to modify the code, but I didn't succeed.
Thank you very much.

 

[Rick Rothstein]

I modified the one-liner that I posted in Message #21 so that the user can specify the count before a delimiter is applied and what that delimiter is. I used Peter's function header with the Optional values pre-assigned in accordance with the OP original request. NOTE - this function does not include a trailing delimiter at the end of the text it returns (if one is needed, it can always be concatenated on the end of the function within the formula that calls it). Also, this function should be able to process a much longer text value than my original function did. And I still do not know if the dynamic array feature of my XL365 is why the code is working for me (that is, I cannot test if this will work in lower versions of Excel or not)... if it does not work in a lower version when committing the formula with Enter, try committing the formula with CTRL+SHIFT+ENTER and see if that makes it work.

Function AddCommas(S As String, Optional Where As Long = 9, Optional Delim As String = ",")
  AddCommas = Replace(Application.Trim(Join(Evaluate("MID(""" & S & """,COLUMN(A:ZZ)*" & Where & "-" & Where & "+1," & Where & ")&"" """), "")), " ", Delim)
End Function