Delimiter With Commas

[bnighgrow]

Hello,

I have a question about separating a text column by commas.

So If my column has 12 characters I would like to know how to add a comma every 4 characters. Regardless of how many characters that there is?

 

[lrobbo314]

Not entirely sure if this is looking for but the following UDF will enter a comma every 4 characters.

Function iCom(r As Range)
Dim str As String
Dim x As Integer
str = r
Dim b As Boolean
b = True
For x = 1 To Len(str)
    If b = True Then
        If x Mod 4 = 0 And Mid(str, x, 1) <> "," Then
            str = Left(str, x) & "," & Right(str, Len(str) - x)
            b = False
        End If
    Else
        If x Mod 5 = 0 And Mid(str, x, 1) <> "," Then
            str = Left(str, x) & "," & Right(str, Len(str) - x)
        End If
    End If
Next x
iCom = str
End Function

 

[bnighgrow]

For some reason for a number like

[TABLE="width: 455"]
<colgroup><col width="455"></colgroup><tbody>[TR]
[TD="class: xl63, width: 455"]010501080111 gets displayed as 0105, 01080,111?

Other numbers it works for like 02180252 is displayed like 0218,0252.

Another one that doesnt work is 0192019301950196 gets displayed
[TABLE="width: 368"]
<colgroup><col width="368"></colgroup><tbody>[TR]
[TD="width: 368"]0192,01930,1950,196.

Any Ideas
Thanks
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcol]

This graceless formula will handle up to 24 characters

=LEFT(MID(A2,1,4)&","&MID(A2,5,4)&","&MID(A2,9,4)&","&MID(A2,13,4)&","&MID(A2,17,4)&",",FIND(",",MID(A2,1,4)&","&MID(A2,5,4)&","&MID(A2,9,4)&","&MID(A2,13,4)&","&MID(A2,17,4)&",",LEN(A2))-1)

But maybe a UDF would be better suited to the job

Function SplitString(rng As Range, SplitLength As Long, Optional Delimiter As String = ",") As String
    Dim n As Long
    
    If rng = "" Then Exit Function
    
    For n = 0 To Len(rng) Step SplitLength
        If SplitString = "" Then
            SplitString = Mid(rng, n + 1, SplitLength)
        Else
            SplitString = SplitString & Delimiter & Mid(rng, n + 1, SplitLength)
        End If
    Next
    SplitString = Left(SplitString, Len(SplitString) - Len(Delimiter))
End Function

Enter in Excel as

=SplitString(A2,4)

Where A2 (Must be a single Cell) is the string to split, and 4 is the length of each split.

To change the delimiter ....

=SplitString(A2,4,"-")

Where "-" is your chosen delimiter, in this case a hyphen/minus sign.
Multiple characters should work as a delimiter e.g. " - "

 

[bnighgrow]

Flawless.

Thank You So Much.

Brandon