Page 1 of 3 123 LastLast
Results 1 to 10 of 22

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

This is a discussion on Insert alt enter into a cell or a column with defined comma counted within the Excel Questions forums, part of the Question Forums category; Dear Sir, I am looking for a code to insert alt-enter into a cell or a column with a defined ...

  1. #1
    Board Regular
    Join Date
    Mar 2008
    Posts
    80

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

    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

  2. #2
    Board Regular
    Join Date
    Jan 2008
    Posts
    8,004

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

    Hi Kevin, Select you cell, Run the code.
    Data Limited per line to 5 values.
    Code:
    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

  3. #3
    Board Regular
    Join Date
    Mar 2008
    Posts
    80

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

    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

  4. #4
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

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

    =AddLF(G10,",",5)
    Code:
    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

  5. #5
    Board Regular
    Join Date
    Mar 2008
    Posts
    80

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

    Hi Jindon,

    How to run your code?

    Kevin

  6. #6
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

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

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

    =AddLF(G10,",",5)

  7. #7
    Board Regular
    Join Date
    Mar 2008
    Posts
    80

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

    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

  8. #8
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

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

    Is the cell formatted as Wrap text ?

  9. #9
    Board Regular
    Join Date
    Mar 2008
    Posts
    80

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

    Yes, the cell is formatted as Wrap text.

  10. #10
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

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

    How about
    Code:
    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

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com