Split multiline cell in columns

  • Hi,
    I have a column with multilines cells that I'd like to split in the next columns. Each line in the cell is a couple (name, value):
    Cell A1:
    Titi&20
    Toto&3
    Tata&321


    I've added columns for B1=Titi , C1=Toto and D1=Tata and I'd like to have a macro that fill columns B, C, D with values in the multi-lines A column:
    B2=20 C2=3 D2=321


    Would you know how to do it ?
    Many thanks,
    Alain

  • Re: Split multiline cell in columns


    Thanks for your help. Very good news if you think it is not that hard. It is not neither that easy. See an example file in attachment with initial table (Column A) and what results I'd like to automatically (macro or 1 button) generate in column B,C,D,E,F,G,H. I tried to write a function that would search for a word (the category name, eg. Terry B) and return the next characters after the delimiter '&' up to the next line break but I did not succeed.
    Some further information to consider for column A:
    - I can have several tens of categories in column A, 1 category per line in a same cell
    - Delimiter is '&' between category and its value, linebreaks between new couple (category, value)
    - we cannot assume a fixed length for the category names, it can be 1 word or several words
    - it might happen that all cells in column A don't have exactly the same list of categories, people sometimes filled only categories that are relevant to them. Anyway, we know the full list of categories, so the titles for columns for B to H are known (list of all categories)



    Lannick
    [TABLE="width: 811"]

    [tr]


    [td]

    Effort

    [/td]


    [td]

    Alain

    [/td]


    [td]

    John T

    [/td]


    [td]

    Terry B

    [/td]


    [td]

    Mike

    [/td]


    [td]

    Wielfried

    [/td]


    [td]

    Cathy Brown

    [/td]


    [td]

    P1

    [/td]


    [/tr]


    [tr]


    [td]

    Alain&0
    John T&5
    Terry B&254
    Mike&3
    Wielfried&13
    Cathy Brown&1200
    P1&0

    [/td]


    [TD="align: right"]0[/TD]
    [TD="align: right"]5[/TD]
    [TD="align: right"]254[/TD]
    [TD="align: right"]3[/TD]
    [TD="align: right"]13[/TD]
    [TD="align: right"]1200[/TD]
    [TD="align: right"]0[/TD]

    [/tr]


    [tr]


    [td]

    Alain&10
    Terry B&3
    Mike&43
    Cathy Brown&640
    P1&0

    [/td]


    [TD="align: right"]10[/TD]
    [TD="align: right"][/TD]
    [TD="align: right"]3[/TD]
    [TD="align: right"]43[/TD]
    [TD="align: right"][/TD]
    [TD="align: right"]640[/TD]
    [TD="align: right"]0[/TD]

    [/tr]


    [/TABLE]

  • Re: Split multiline cell in columns


    This should do

  • Re: Split multiline cell in columns


    Thanks a lot Jindon,
    it works just perfectly!
    I tried to write an excel function that would search for a word (the category name, eg. Terry B) and return the next characters after the delimiter '&' up to the next line break but I did not succeed. Isn't it possible without VBA ?
    Alain

  • Re: Split multiline cell in columns


    Or try, in cell B2 filled across and down:


    =VALUE(TRIM(MID($A2,FIND(B$1,$A2)+LEN(B$1)+1,IFERROR(FIND("¬",SUBSTITUTE($A2,CHAR(10),"¬"),FIND(B$1,$A2)),LEN($A2))-(FIND(B$1,$A2)+LEN(B$1)+1))))

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Split multiline cell in columns


    A small change to the excellent formula of Batman. If not would add that one in red, and if that P1&.. in the last line, would be followed by 2 or 3 digits, the result of the formula would have a digit less.


    =VALUE(TRIM(MID($A2;FIND(B$1;$A2)+LEN(B$1)+1;IFERROR(FIND("¬";SUBSTITUTE($A2;CHAR(10);"¬");FIND(B$1;$A2));LEN($A2)+1)-(FIND(B$1;$A2)+LEN(B$1)+1))))

  • Re: Split multiline cell in columns


    Hi,
    is there any way to build a formula that search for a key in a string and return the word delimited by the next delimiter ?
    In my case, the delimiter between couples (key, value) is linebreak, the delimiter between keys and values is '&'. Here is my cell, a multi-line cell :
    A1:
    Alain&0
    John T&5
    Terry B&254
    Mike&3
    Wielfried&13
    Cathy Brown&1200


    When searching Mike, I'd like to return 3. Then, I would manually create the corresponding columns in B, C, D for the different keys.


    Jindon,
    your vba overwrite columns from B. As my column would be in the middle of some others, is there a way to either inserting new columns after A to fill with the values or create additional columns at the end of the sheet so I don't overwrite any data.


    Thanks for your help,
    Lannick

  • Re: Split multiline cell in columns


    Jindon,
    your vba assumes that values are in column A. In fact, it can be anywhere. In addition, vba overwrite columns on the right, while I have already some other columns on the right. Is there a way to find for the column "Effort" and add additional columns on the rights for each category. It won"t overwrite my existing columns.
    Thanks,
    Alain

  • Re: Split multiline cell in columns


    I found my way with vba, looking for the column first then adding additional columns for keys. See my vba code below


    If anybody would be able to propose a formula to search for a key in a string and return the word delimited by the next delimiter, see my previous post, it would be great.


    Sub Effort()
    ' Look for columns "Effort" and find the column number
    Dim strSearch As String
    Dim aCell As Range
    strSearch = "Efforts"


    ' Set aCell = Rows(1).Find(What:=strSearch, LookIn:=xlValues,
    Set aCell = Cells(1).CurrentRegion.Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)


    If aCell Is Nothing Then
    MsgBox "Column " & strSearch & " NOT Found"
    Exit Sub
    End If
    ' MsgBox "Column " & strSearch & " Found in Cell " & aCell.Address & " and the Cell Column Number is " & aCell.Column


    ' Loop on (key, values) in the column : pattern is key&value, delimiter between couples of (key,value) is a linebreak
    Dim a, i As Long, m As Object, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    With Cells(1).CurrentRegion.Columns(aCell.Column).Offset(aCell.Row - 1, 0)
    ' With Columns(aCell.Column)
    .Borders.LineStyle = xlNone
    ' .Offset(, 1).ClearContents
    a = .Value
    With CreateObject("VBScript.RegExp")
    .Global = True: .MultiLine = True
    .Pattern = "^([^&]+)&([^\r\n]+)"
    For i = 2 To UBound(a, 1)
    For Each m In .Execute(a(i, 1))
    If Not dic.exists(m.submatches(0)) Then
    dic(m.submatches(0)) = dic.Count + 2
    If UBound(a, 2) < dic.Count + 1 Then
    ReDim Preserve a(1 To UBound(a, 1), 1 To dic.Count + 1)
    End If
    a(1, dic.Count + 1) = m.submatches(0)
    End If
    a(i, dic(m.submatches(0))) = m.submatches(1)
    Next
    Next
    End With
    'Insert new columns
    For i = 1 To dic.Count
    Columns(aCell.Column + 1).Insert shift:=xlToRight
    Next i
    With .Resize(, dic.Count + 1)
    .Value = a: .Borders.Weight = 2
    .VerticalAlignment = xlCenter
    End With
    End With
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!