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,

 

[duggie33]

Is the string length always going to be in multiples of nine? My formula might not give the expected results if the length of the input is not in multiples of nine.

=TEXTJOIN(",",TRUE,MID(A1,SEQUENCE(ROUNDDOWN(LEN(A1)/9,0),1,0,9)+1,9))&","

I am positive it can be cleaned-up/improved.

Hope that helps,

Doug

 

[grussell070769]

=TEXTJOIN(",",TRUE,MID(A1,SEQUENCE(ROUNDDOWN(LEN(A1)/9,0),1,0,9)+1,9))&","

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

 

[Joe4]

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

Some of those functions may not be available in Excel 2021

 

[lrobbo314]

Here is a way using PowerQuery.

EXCEL
	A	B	C
1	Numbers		Numbers
2	144849383600226994968816910897204120078656326273603444077371702		144849383,600226994,968816910,897204120,078656326,273603444,077371702
3	010882765421280172524857202127605316837668282342649953503691		010882765,421280172,524857202,127605316,837668282,342649953,503691
4	6023367054925382409056409728100834015337960373526982734702021		602336705,492538240,905640972,810083401,533796037,352698273,4702021
5	730335284668288737472789869655295910221946556260013423000966763456		730335284,668288737,472789869,655295910,221946556,260013423,000966763,456
6	686572419255530859260374435598580500404692808689497106526907314		686572419,255530859,260374435,598580500,404692808,689497106,526907314
7	473322394931064041827847062669653346095951698024943653223799177		473322394,931064041,827847062,669653346,095951698,024943653,223799177
Sheet7

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Insert = Table.TransformColumns(Source,{{"Numbers", (txt)=> 
    let 
        lst = List.Generate(() => 0, each _ <= Number.Round(Text.Length(txt)/10,0), each _ + 1, each _ * 9 + _),
        ins = Text.RemoveRange(List.Accumulate(lst,txt,(s,c)=> Text.Insert(s,c,",")),0)
    in
        ins
    }})
in
    Insert

 

[Fluff]

The return is coming back #NAME?

Are you using 2021 as per your profile?

 

[grussell070769]

@Fluff, sorry I need to update that, I am on Office Professional Plus 2016

 

[Fluff]

That's why you get #name as you won't have either textjoin or sequence

 

[Micron]

In case a vba solution would help (which I think would be not version dependent)

Function InsertComma(strIn As String) As String
Dim i As Integer, n As Integer
Dim strOut As String

n = 1
For i = 1 To Len(strIn) / 9
     strOut = strOut & Mid(strIn, n, 9) & ","
     n = n + 9
Next
Debug.Print strOut
InsertComma = strOut

End Function

 

[Fluff]

Assuming the length of the string varies, I think VBA is your only option.