Insert alt enter into a cell or a column with defined comma counted

[Kevincwk2000]

Dear Sir,

I am looking for a code to insert alt-enter into a cell or a column with a defined comma count per line, saying 5

is

C1,C2,C3,C4,C5,C6,C7
C8,C9,C10,C11

want to change the cell(s) as

C1,C2,C3,C4,C5,
C6,C7,C8,C9,C10,
C11

thanks,
Kevin

 

[MickG]

Hi Kevin, Select you cell, Run the code.
Data Limited per line to 5 values.

Dim oCt, oNum As Integer, nNum As String
oCt = Split(Selection.Value, ",")

For oNum = 0 To UBound(oCt)
        If oNum <> 0 And (oNum + 1) Mod 5 = 0 Then
                nNum = nNum & oCt(oNum) & "," & Chr(10)
            Else
                nNum = nNum & oCt(oNum) & ","
        End If
Next oNum

With Range(Selection.Address)
.Value = nNum
.WrapText = True
End With

Mick

 

[Kevincwk2000]

Hi Mick,

run the code the cell will become,

C1,C2,C3,C4,C5,
C6,C7,
C8,C9,C10,
C11,

it is not my expected result.

thanks,
Kevin

 

[jindon]

=AddLF(G10,",",5)

Function AddLF(txt As String, delim As String, n As Long) As String
Dim x, i As Long
x = Split(txt, delim)
For i = 0 To UBound(x) Step n
    If i <> 0 Then x(i) = vbLf & x(i)
Next
AddLF = Join(x, delim)
End Function

 

[Kevincwk2000]

Hi Jindon,

How to run your code?

Kevin

 

[jindon]

Paste the code onto a sandard module then use in cell like

=AddLF(G10,",",5)

 

[Kevincwk2000]

Hi Jindon,

then the cell become

C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11

seems the code removed alt-enter only but no modify the cell into 5 comma per line.

Kevin

 

[jindon]

Is the cell formatted as Wrap text ?

 

[Kevincwk2000]

Yes, the cell is formatted as Wrap text.

 

[jindon]

How about

Function AddLF(txt As String, delim As String, n As Long) As String
Dim x, i As Long
x = Split(Replace(txt, vbNewLine, ""), delim)
For i = 0 To UBound(x) Step n
    If i <> 0 Then x(i) = vbNewLine & x(i)
Next
AddLF = Join(x, delim)
End Function