How can I add a comma & space after every two digits in Excel and then format the text?

user_11047281 0
Viewed 775

Here's the table I'm working with:

enter image description here

First off, how can I separate the string of numbers in column C so that they have a comma and space after every two digits?

I was able to partially finish it, but for some reason I can't grab the last two digits. I used this formula:

=MID(C4,1,2)&", "&MID(C4,3,2)&", "&MID(C4,6,2)&", "&MID(C4,10,2)&", "&MID(C4,13,2)&", "&MID(C4,16,2)

After that I need to somehow format that string to look like the example in column E, that's where I'm really confused. Maybe I don't even need the first step?

Any help is appreciated. Thanks

excel
1 Answers

If you already have access to TEXTJOIN formula then it will quickly become your best friend :-)

I assume that your values are in column C (starting from cell C1) and that the column is formatted as text.

Try:

="""stats"":["&TEXTJOIN(", ",TRUE,MID(C1,ROW($A$1:INDEX(A:A,LEN(C1)/2))*2-1,2))&"]"

If your Office 365 version is not using dynamic arrays yet, try entering the formula with Ctrl+Shift+Enter:

enter image description here

user_106719260
Related