The formula for separating the components of a cell into Multiple cells in the same column

[Hany ali]

hello every one ,i want to separat cell contents between this sigh - From Column B to multiple cell in the same Column as you see in Result in Column F With the duplication of the name in the column A to the column E by the Code of the chapter as you can see in the picture

 

[herofox]

hi -you can to Use This Code

Sub Demo()
    Dim rng As Range, Lstrw As Long, c As Range
    Dim SpltRng As Range
    Dim i As Integer
    Dim Orig As Variant
    Dim txt As String
        With Sheet1
    .Range("D1:E9999").ClearContents
    Lstrw = Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = Range("A1:A" & Lstrw)
    
    For Each c In rng.Cells
        Set SpltRng = c.Offset(, 1)
        txt = SpltRng.Value
        Orig = Split(txt, "-")
    
        For i = 0 To UBound(Orig)
            Cells(Rows.Count, "D").End(xlUp).Offset(1) = c
            Cells(Rows.Count, "D").End(xlUp).Offset(, 1) = Orig(i)
        Next i

    Next c
    End With
End Sub

 

[Fluff13]

Another option

Sub HaniAli()
   Dim Ary As Variant, Nary As Variant, Sp As Variant
   Dim r As Long, nr As Long, i As Long
  
   Ary = Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * 100, 1 To 2)
   For r = 1 To UBound(Ary)
      Sp = Split(Ary(r, 2), "-")
      For i = 0 To UBound(Sp)
         nr = nr + 1
         Nary(nr, 1) = Ary(r, 1)
         Nary(nr, 2) = Sp(i)
      Next i
   Next r
   Range("E:F").EntireColumn.Value = ""
   Range("E1").Resize(nr, 2).Value = Nary
End Sub

 

[Hany ali]

Thank you so much to all of you ... that's really what is needed

 

[Fluff13]

Glad to help & thanks for the feedback.

 

[Peter Bartholomew]

I normally do dynamic array formulas but the varying length codes made that a bit of an effort so here is a Power Query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Source"]}[Content],
    SplitColumns = Table.SplitColumn(Source, "Code", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Code.1", "Code.2", "Code.3", "Code.4"}),
    Unpivoted = Table.UnpivotOtherColumns(SplitColumns, {"Name"}, "Attribute", "Code"),
    Output = Table.RemoveColumns(Unpivoted,{"Attribute"})
in
    Output

 

[bosco_yip]

Formula solution

Individual data list formula method

1] In D2, formula copied down :

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($2:$9)/(LEN($B$2:$B$9)-LEN(SUBSTITUTE($B$2:$B$9,"-",""))+1>=COLUMN($A:$J)),ROW(A1))),"")

2] In E2, formula copied down :

=IF(D2="","",TRIM(MID(SUBSTITUTE("-"&VLOOKUP(D2,$A$2:$B$9,2,0),"-",REPT(" ",99)),COUNTIF(D$2:D2,D2)*99,99)))

Single formula method
(the formula using CONCAT function of which required Office 365 or Excel 2019 version)

In G2, array (CSE) formula copied right to H2 and all copied down :

=IFERROR(FILTERXML("<a><b>"&CONCAT(SUBSTITUTE($B$2:$B$9&"-","-","</b><b>"&$A$2:$A$9&"</b><b>"))&"</b></a>","a/b[position() mod 2 ="&0+(COLUMN(A1)=2)&"]["&ROW(A1)&"]"),"")

 

[AlanSidman]

Here is another Power Query Solution you may find beneficial

let
    Source = Excel.CurrentWorkbook(){[Name="Source"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Code", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Code")
in
    #"Split Column by Delimiter"

 

[Peter Bartholomew]

@AlanSidman
I had been focussed on a formula solution

= LET(
  splitCodes, SPLIT.TEXT(Source[Code],"-"),
  array, HSTACK(Source[Name], splitCodes),
  list, UNPIVOT(array,,1,,,0),
  reduced, SLICES(list,0,{1,3}),
  code, SLICES(list,0,3),
  FILTER(reduced, code<>"") )

that uses Charles Williams's FastExcel add-in. I decided there was no point in posting that, so went with the PQ solution instead.
I just failed to adjust my thinking to the split to row option; much better than unpivoting!

Meanwhile @bosco_yip has come up with a miracle of ingenuity with traditional spreadsheet methods. Maybe I will take a look at them using modern Excel!