excel吧 关注：280,329贴子：1,536,868
菠萝蜜
Option Explicit
Const STR As String = ":"
Sub 去除括号内指定字符()
Dim a, i, j, k, n
a = [a1].CurrentRegion.Resize(, 1).Value
For i = 1 To UBound(a)
For j = 1 To Len(a(i, 1))
If InStr(Mid(a(i, 1), j, 1), "(") Then
n = 1
For k = j + 1 To Len(a(i, 1))
If InStr(Mid(a(i, 1), k, 1), ")") Then n = n - 1
If InStr(Mid(a(i, 1), k, 1), "(") Then n = n + 1
If n = 0 Then j = k: Exit For
If Mid(a(i, 1), k, 1) = STR Then Mid(a(i, 1), k, 1) = "?"
Next
End If
Next
a(i, 1) = Replace(a(i, 1), "?", vbNullString)
Next
[b1].Resize(UBound(a)) = a
End Sub
let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
更改的类型 = Table.TransformColumnTypes(源,{{"列表", type text}}),
已添加自定义 = Table.AddColumn(更改的类型, "A", each Text.Split(Web.Page("<script>document.write('"&[列表]&"'.match(/\(\d+:\d+\)/gm))</script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0},",")),
已添加自定义1 = Table.AddColumn(已添加自定义, "B", each Text.Split(Text.Remove(Web.Page("<script>document.write('"&[列表]&"'.match(/\(\d+:\d+\)/gm))</script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0},":"),",")),
X = Table.AddColumn(已添加自定义1, "Z", each List.Zip({[A],[B]})),
已添加自定义2 = Table.AddColumn(X, "自定义", each List.Accumulate([Z],[列表],(x,y)=>Text.Replace(x,y{0},y{1}))),
删除的其他列 = Table.SelectColumns(已添加自定义2,{"自定义"})
in
删除的其他列
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
更改的类型 = Table.TransformColumnTypes(源,{{"列表", type text}}),
已添加自定义 = Table.AddColumn(更改的类型, "A", each Text.Split(Web.Page("<script>document.write('"&[列表]&"'.match(/\(\d+:\d+\)/gm))</script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0},",")),
已添加自定义1 = Table.AddColumn(已添加自定义, "B", each Text.Split(Text.Remove(Web.Page("<script>document.write('"&[列表]&"'.match(/\(\d+:\d+\)/gm))</script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0},":"),",")),
X = Table.AddColumn(已添加自定义1, "Z", each List.Zip({[A],[B]})),
已添加自定义2 = Table.AddColumn(X, "自定义", each List.Accumulate([Z],[列表],(x,y)=>Text.Replace(x,y{0},y{1}))),
删除的其他列 = Table.SelectColumns(已添加自定义2,{"自定义"})
in
删除的其他列
