How to add comma after every 4 digits which length is 1000 in excel?

Question

I want to add comma after every 4 digits in, for example, 45700153920458799220 . I have tried this =LEFT(B1,4)&","&MID(B1,5,4)&","&RIGHT(B1,4) for 39297500424. But how about more than 12 length?

Answer 1

Avoid INDIRECT, VBA, macro user-defined functions, and hardcoded string lengths as in the existing three answers by using Excel's new spilling formulas (see this and that).

Basic formula in B2:

=TEXTJOIN(",", FALSE, BYROW(WRAPROWS(MID(A2,SEQUENCE(LEN(A2)),1),4, ""), LAMBDA(array, CONCAT(array))))

Formula counting from left in C2:

=LET(string, A2, 
     spacing, 4, 
     delimiter, ",", 
chars, MID(string,SEQUENCE(LEN(string)),1), charsWithFrontPadding, VSTACK(IF(SEQUENCE(spacing-MOD(LEN(string), spacing)), ""), chars), TEXTJOIN(delimiter, TRUE, BYROW(WRAPROWS(charsWithFrontPadding,spacing,""), LAMBDA(array, CONCAT(array)))))

As a last example, this is useful to me for binary number display. Using LET makes it easy to quickly change delimiter to an underscore and group by 8 characters for example in C4:

=LET(string, A4,
     spacing, 8,
     delimiter, "_",
chars, MID(string,SEQUENCE(LEN(string)),1), charsWithFrontPadding, VSTACK(IF(SEQUENCE(spacing-MOD(LEN(string), spacing)), ""), chars), TEXTJOIN(delimiter, TRUE, BYROW(WRAPROWS(charsWithFrontPadding,spacing,""), LAMBDA(array, CONCAT(array)))))

For binary display, it is common to add "0" padding at the front. That can be accomplished with this more universally-applicable formula:

=LET(

string,
A10,

delimiterInterval,
8,

delimiterChar,
"_",

paddingChar,
"0",

stringCharArray,
MID(string,SEQUENCE(LEN(string)),1),
qtyOfPadding,
MOD(LEN(string),delimiterInterval),
paddingCharArray,
IF(SEQUENCE(delimiterInterval - qtyOfPadding), paddingChar),
paddedStringCharArray,
IF(qtyOfPadding=0, stringCharArray, VSTACK(paddingCharArray, stringCharArray)),

TEXTJOIN(delimiterChar, 
         TRUE, 
         BYROW(WRAPROWS(paddedStringCharArray, delimiterInterval), 
               LAMBDA(row, CONCAT(row))))
)

Simplified even further with LET/LAMBDA in order to call as easily as byteFormat(A10) or even byteFormat(A1:A10):

=LET(
addDelimiterEveryXChar, LAMBDA(delimiterInterval,delimiterChar,paddingChar,string,
   LET(
      stringCharArray, MID(string,SEQUENCE(LEN(string)),1),
      qtyOfPadding, MOD(LEN(string),delimiterInterval),
      paddingCharArray, IF(SEQUENCE(delimiterInterval - qtyOfPadding), paddingChar),
      paddedStringCharArray, IF(qtyOfPadding=0, stringCharArray, VSTACK(paddingCharArray, stringCharArray)),
      TEXTJOIN(delimiterChar, TRUE, BYROW(WRAPROWS(paddedStringCharArray, delimiterInterval),
                                          LAMBDA(row, CONCAT(row))))
   )),
byteFormatSingle, LAMBDA(string, addDelimiterEveryXChar(8, "_", "0", string)),
byteFormat, LAMBDA(a, MAP(a, LAMBDA(cell, byteFormatSingle(cell)))),

byteFormat(A1:A10)
)

Answer 2

If your number is more than 16 in length, as is your first example, you will need to enter it as text, as Excel's limit is 15 digits.

One way, with your string in A1, would be:

A2:  =IF(RIGHT(REPLACE(A1,4*ROWS($1:1)+ROWS($1:1),0,","),1)=",","",REPLACE(A1,4*ROWS($1:1)+ROWS($1:1),0,","))

and fill down until you get blanks, even if you have thousands of digits.

Then, in some other cell, (B1 in my screenshot below), enter:

=LOOKUP(2,1/LEN($A:$A),$A:$A)

which will return the last non-blank entry in column A, which is your result.

For something a bit more flexible, a UDF (user defined function) written in VBA is suggested. The following UDF has some Optional arguments allowing you to specify, if you want, a different character to insert, and different spacing. If you don't specify anything other than the string, it will insert commas every fourth.

It also demonstrates a use of the VBA Format function.

---

Option Explicit
Function InsertChar(STR As String, Optional sInsertCharacter As String = ",", Optional lSpacing As Long = 4) As String
    Dim sCharString As String
    Dim sFormatString As String
    Dim sTemp As String
    Dim I As Long

For I = 1 To lSpacing
    sCharString = sCharString & "&"
Next I
sCharString = sCharString & sInsertCharacter

For I = 0 To Len(STR) \ lSpacing
    sFormatString = sFormatString & sCharString
Next I

sFormatString = "!" & Left(sFormatString, Len(sFormatString) - 1)

sTemp = Format(STR, sFormatString)
If Right(sTemp, 1) = "," Then sTemp = Left(sTemp, Len(sTemp) - 1)

InsertChar = sTemp

End Function

---

Answer 3

The following User Defined Function will insert a comma after every 4 characters, starting from the left:

Public Function fracture(r As Range) As String
    Dim s As String, s2 As String
    Dim L As Long, i As Long

    s = r(1).Text
    L = Len(s)
    s2 = ""
    For i = 1 To L
        s2 = s2 & Mid(s, i, 1)
        If i Mod 4 = 0 Then s2 = s2 & ","
    Next i
    fracture = s2
End Function

Examples:

NOTE:

• the UDF() will work with either numeric characters or text characters
• the UDF() will place a comma at the end of the string if the string length is a multiple of 4

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=fracture(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

Answer 4

Try this

=LEFT(B1,4)&","&MID(B1,5,4)&","&MID(B1,9,4)&","&MID(B1,13,4)

continue .... I wouldn't use RIGHT unless you know your number length is a multiple of 4 as it will give incorrect result.