let
Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
#"Разделить столбец по разделителю" = Table.ExpandListColumn(Table.TransformColumns(Источник, {{"Столбец2", Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Столбец2")
in
#"Разделить столбец по разделителю"
Sub test()
myTransform Range("A1:B2"), Workbooks.Add(1).Sheets(1).Cells(1, 1)
End Sub
Sub myTransform(rangeSource As Range, rangeTarget As Range)
Dim bb As Variant
Dim urr As Variant
Dim brr As Variant
Dim arr As Variant
arr = rangeSource.Columns("A:B")
Dim yy As Long
Dim uu As Long
Dim ii As Long
For ii = 0 To 1
If ii = 1 Then
If uu > 0 Then
ReDim urr(1 To uu, 1 To 2)
uu = 0
Else
Exit For
End If
End If
For yy = 1 To UBound(arr, 1)
If Not IsEmpty(arr(yy, 2)) Then
brr = Split(arr(yy, 2), ";")
For Each bb In brr
uu = uu + 1
If Not IsEmpty(urr) Then
urr(uu, 1) = arr(yy, 1)
urr(uu, 2) = Trim(bb)
End If
Next
End If
Next
Next
If IsEmpty(urr) Then
With rangeTarget.Parent.Parent
If .Path = "" Then .Close False
End With
Else
rangeTarget.Resize(UBound(urr, 1), UBound(urr, 2)) = urr
End If
End Sub
memo, что-то перемудрено =IF(ROWS(D$1:D1)>SUM(1+LEN(B$1:$B$2)-LEN(SUBSTITUTE($B$1:$B$2;";";)));"";INDEX(A$1:A$2;MATCH(ROWS(D$1:D1)-1;MMULT(--(ROW(A$1:A$2)>TRANSPOSE(ROW(A$1:A$2)));1+LEN($B$1:$B$2)-LEN(SUBSTITUTE($B$1:$B$2;";";))))))
вторая аналогично в этой части. + =IF(D1="";"";TRIM(MID(SUBSTITUTE(";"&INDEX($B$1:$B$2;MATCH(ROWS(E$1:E1)-1;MMULT(--(ROW($B$1:$B$2)>TRANSPOSE(ROW($B$1:$B$2)));1+LEN($B$1:$B$2)-LEN(SUBSTITUTE($B$1:$B$2;";";)))));";";REPT(" ";99));COUNTIF(D$1:D1;D1)*99;99)))