CODEWITHSUNDEEP
excel
Gil
Gil

Reputation: 77

(Not text to columns) - break 1 colums into many with delimiter

I have an Excel sheet with 1 column and many lines. I have a delimiter (;) at the position where I want to split the column to a new column, to arrange the data. Each colum will contain between 5 to 10 rows (its random) with informations like name, adress, etc

Basically I want to transform my sheet from the yellow part (A) to what you see in black. I tried to play with the Pivot or the Text to columns, but without success :-(

Thank you for your help

enter image description here

Upvotes: 0

Views: 51

Answers (2)

JvdV
JvdV

Reputation: 75840

Just for fun using dynamic arrays in Microsoft365:

enter image description here

Formula in C1:

=FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,$A1:$A17)&"</s></t>","//s[count(preceding::*[.=';'])="&COLUMN(A1)-1&"][.!=';']")

Drag to the right...

  • "<t><s>"&TEXTJOIN("</s><s>",,$A1:$A17)&"</s></t>" - Create a valid xml-string concatenating all values from the appropriate range in column A.
  • //s - Return those nodes where:
    • count(preceding::*[.=';'])="&COLUMN(A1)-1&"] - The count of preceding nodes that are equal to ";" is based on the COLUMN(A1)-1 part and;
    • [.!=';'] - Nodes themselves are not equal to ";".

Upvotes: 3

urdearboy
urdearboy

Reputation: 14580

If open to VBA solution

Sub Try()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim lr As Long, r As Long, c As Long, i As Long

lr = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
r = 1
c = 3

Application.ScreenUpdating = False
    For i = 1 To lr
        If ws.Range("A" & i) = ";" Then
            c = c + 1
            r = 1
        Else
            ws.Cells(r, c).Value = ws.Range("A" & i).Value
            r = r + 1
        End If
    Next i
Application.ScreenUpdating = True

End Sub

enter image description here

Upvotes: 2

Related Questions