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,

 

[lrobbo314]

Yes, clearly VBA is the only option...

 

[kevin9999]

An ugly formula option...

Book1
	A
1	144849383600226994968816910897204120078656326273603444077371702
2	144849383,600226994,968816910,897204120,078656326,273603444,077371702,
Sheet2

Cell Formulas
Range	Formula
A2	A2	=MID(A1,1,9)&","&MID(A1,10,9)&","&MID(A1,19,9)&","&MID(A1,28,9)&","&MID(A1,37,9)&","&MID(A1,46,9)&","&MID(A1,55,9)&","&MID(A1,64,9)

 

[lrobbo314]

An ugly formula option...

Book1
	A
1	144849383600226994968816910897204120078656326273603444077371702
2	144849383,600226994,968816910,897204120,078656326,273603444,077371702,
Sheet2

Cell Formulas
Range	Formula
A2	A2	=MID(A1,1,9)&","&MID(A1,10,9)&","&MID(A1,19,9)&","&MID(A1,28,9)&","&MID(A1,37,9)&","&MID(A1,46,9)&","&MID(A1,55,9)&","&MID(A1,64,9)

Eww. But hey, it works.

 

[MARK858]

An ugly formula option...

Beauty is in the eye of the beholder... Sorry

 

[Peter_SSs]

Here is another, non-looping, user-defined function. It also has options to use a different delimiter to a commas and also to place that delimiter after a different number of characters (column C)
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

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	C
1	144849383600226994968816910897204120078656326273603444077371702	144849383,600226994,968816910,897204120,078656326,273603444,077371702,	1448#4938#3600#2269#9496#8816#9108#9720#4120#0786#5632#6273#6034#4407#7371#702
Add commas

Cell Formulas
Range	Formula
B1	B1	=AddCommas(A1)
C1	C1	=AddCommas(A1,4,"#")

 

[Tom.Jones]

Here is another, non-looping, user-defined function. It also has options to use a different delimiter to a commas and also to place that delimiter after a different number of characters (column C)

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

Peter,

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

Thank you.

 

[Micron]

Try modifying what I wrote earlier as InsertComma = Left(strOut,Len(strOut)-1)
or after
AddCommas = .Replace(s, "$1" & Delim)
add
AddCommas = Left(AddCommas,Len(AddCommas)-1)

 

[Tom.Jones]

Micron,

Thank you very much.

 

[Micron]

You're welcome.

 

[johnnyL]

The return is coming back #NAME? when I enter the formula. Yes it would always be a string of nines.

Here are the two missing functions to add to a regular module to allow you to use the formula in post # 2:

Function SEQUENCE(Optional ByVal rows As Long = 1, Optional ByVal columns As Long = 1, Optional ByVal start As Long = 1, Optional ByVal step As Long = 1) As Variant      ' Excel 2021
' This custom VBA function takes up to four parameters:
'
'    rows - the number of rows in the output array (default value is 1)
'    columns - the number of columns in the output array (default value is 1)
'    start - the starting value for the sequence (default value is 1)
'    step - the step value for the sequence (default value is 1)
'
'   At a minimum, you have to include a rows, or a columns
'
' To use it in a foumula on the worksheet:
'           Highlight the cells that will be populated by the formula
'           Put the formula in the top cell of the highlighted range ex. =SEQUENCE(10,1)
'           Then do Ctrl+Shift+Enter to enter the formula as an array formula
'
    Dim ArrayColumn     As Long, ArrayRow   As Long
    Dim SequenceValue   As Long
    Dim SequenceArray   As Variant
'
    ReDim SequenceArray(1 To rows, 1 To columns)
    
    SequenceValue = start
    
    For ArrayRow = 1 To rows
        For ArrayColumn = 1 To columns
            SequenceArray(ArrayRow, ArrayColumn) = SequenceValue
            SequenceValue = SequenceValue + step
        Next
    Next
    
    SEQUENCE = SequenceArray
End Function


Function TEXTJOIN(delimiter As Variant, ignore_empty As Boolean, ParamArray TextArray() As Variant) As String           ' Excel 2019
'
' Clone of TextJoin for versions of Excel prior to 2016
' 2016, Eric W, adapted from AConcat by Harlan Grove
' 2021, Eric W, Added multiple delimiter support
'
    Dim Flag                    As Boolean
    Dim ArrayRow                As Long
    Dim TextArrayRow            As Long, LastRowDelimiterArray  As Long
    Dim DelimiterArray()        As String
    Dim CellToJoin              As Variant
    Dim DelimiterValue          As Variant
'
    TEXTJOIN = ""
'
    If TypeOf delimiter Is range Then
        ReDim DelimiterArray(0 To delimiter.Cells.Count - 1)
        ArrayRow = 0
'
        For Each DelimiterValue In delimiter
            DelimiterArray(ArrayRow) = DelimiterValue.value
            ArrayRow = ArrayRow + 1
        Next
    ElseIf IsArray(delimiter) Then
''        ReDim DelimiterArray(0 To UBound(delimiter) - LBound(delimiter) + 1)
        ReDim DelimiterArray(0 To UBound(delimiter) - LBound(delimiter))
        ArrayRow = 0
'
        For Each DelimiterValue In delimiter
            DelimiterArray(ArrayRow) = DelimiterValue
            ArrayRow = ArrayRow + 1
        Next
    Else
        ReDim DelimiterArray(0 To 0)
''        DelimiterArray(0) = delimiter
'
        If IsMissing(delimiter) Then
            DelimiterArray(0) = ""
        Else
            DelimiterArray(0) = delimiter
        End If
    End If
'
    ArrayRow = 0
    LastRowDelimiterArray = UBound(DelimiterArray) + 1
    Flag = False
    
    For TextArrayRow = LBound(TextArray) To UBound(TextArray)
        If TypeOf TextArray(TextArrayRow) Is range Then
            For Each CellToJoin In TextArray(TextArrayRow).Cells
                If CellToJoin = "" And ignore_empty Then
                Else
                    TEXTJOIN = TEXTJOIN & IIf(Flag, DelimiterArray(ArrayRow Mod LastRowDelimiterArray), "") & CellToJoin.value
                    ArrayRow = IIf(Flag, ArrayRow + 1, ArrayRow)
                    Flag = True
                End If
            Next
        ElseIf IsArray(TextArray(TextArrayRow)) Then
            For Each CellToJoin In TextArray(TextArrayRow)
                If CellToJoin = "" And ignore_empty Then
                Else
                    TEXTJOIN = TEXTJOIN & IIf(Flag, DelimiterArray(ArrayRow Mod LastRowDelimiterArray), "") & CellToJoin
                    ArrayRow = IIf(Flag, ArrayRow + 1, ArrayRow)
                    Flag = True
                End If
            Next
        Else
            If TextArray(TextArrayRow) = "" And ignore_empty Then
            Else
                TEXTJOIN = TEXTJOIN & IIf(Flag, DelimiterArray(ArrayRow Mod LastRowDelimiterArray), "") & TextArray(TextArrayRow)
                ArrayRow = IIf(Flag, ArrayRow + 1, ArrayRow)
                Flag = True
            End If
        End If
    Next
End Function

Book1
	A	B	C
1	144849383600226994968816910897204120078656326273603444077371702	144849383,600226994,968816910,897204120,078656326,273603444,077371702,
2
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TEXTJOIN(",",TRUE,MID(A1,SEQUENCE(ROUNDDOWN(LEN(A1)/9,0),1,0,9)+1,9))&","
Press CTRL+SHIFT+ENTER to enter array formulas.