连接数据如何拆分

killalls

comeet 发表于 2024-2-29 14:17

厉害，我试试

killalls

sayhi95 发表于 2024-2-29 14:24
2016及以上版本自带PQ，在数据选项卡下获取和转换数据那个就是PQ功能

好，我都忘记了，我是2019，我试试

qydn

Sub tt()
Dim rn As Range, sj, ss, i%, str$, m%, reg As Object
Set reg = CreateObject("vbscript.regexp")
m = 2
With reg
    .Global = 1
    .Pattern = "[A-Z]+\d+ \d+"
    For Each rn In Range("d2", Cells(Rows.Count, 4).End(xlUp))

无关岁月

killalls 发表于 2024-2-29 14:20
大神你好，你的代码很厉害，我试了一下，能拆分，但数据没有跟着变喔

修改好了

新建 XLSX 工作表 - 副本.7z (17.15 KB, 下载次数: 3)

2024-2-29 14:42 上传

点击文件名下载附件

我的城市你来过

=HSTACK(DROP(TEXTSPLIT(TEXTJOIN("",,REPT(" "&A2:A4,LENB($D$2:$D$4)-LENB(SUBSTITUTE($D$2:$D$4,"/",""))+1)),," "),1),DROP(TEXTSPLIT(TEXTJOIN("",,REPT(" "&B2:B4,LENB($D$2:$D$4)-LENB(SUBSTITUTE($D$2:$D$4,"/",""))+1)),," "),1),TEXTSPLIT(SUBSTITUTE(TEXTJOIN("@",,D2:D4),"/","@"),,"@"))

=IFERROR(MID(H2,FIND(" ",H2)+1,2),CONCAT(IF($F2&$G2&$H2=$A$2:$A$4&$B$2:$B$4&$D$2:$D$4,$C$2:$C$4,"")))

hcm19522

(先)G2:H8{=OFFSET(A$1,SMALL(IF(LEN($D$2:$D$4)-LEN(SUBSTITUTE($D$2:$D$4,"/",))+1>=COLUMN($A:$I),{1;2;3},99),ROW(A1)),)&""

(中)J2:J8{=IF(G2="","",TRIM(MID(SUBSTITUTE(T(OFFSET(D$1,SMALL(IF(LEN(D$2:D$4)-LEN(SUBSTITUTE(D$2:D$4,"/",))+1>=COLUMN(A:I),{1;2;3}),ROW(D1)),)),"/",REPT(" ",99)),RIGHT(SMALL(IF(LEN(D$2:D$4)-LEN(SUBSTITUTE(D$2:D$4,"/",))+1>=COLUMN(A:I),{1;2;3}/1%+COLUMN(A:I)),ROW(D1)),2)*99-98,99)))

(後)I2:I8=IF(G2="","",IFERROR(MID(J2,FIND(" ",J2)+1,9),OFFSET(C$1,SMALL(IF(LEN(D$2:D$4)-LEN(SUBSTITUTE(D$2:D$4,"/",))+1>=COLUMN(A:I),{1;2;3}),ROW(A1)),)))

killalls

无关岁月 发表于 2024-2-29 14:42
修改好了

试过，暂时没有问题，感谢大神

killalls

我的城市你来过 发表于 2024-2-29 15:06
=HSTACK(DROP(TEXTSPLIT(TEXTJOIN("",,REPT(" "&A2:A4,LENB($D$2:$D$4)-LENB(SUBSTITUTE($D$2:$D$4,"/","") ...

感谢大神哈，我先弄好365再试试

killalls

hcm19522 发表于 2024-2-29 15:56
(先)G2:H8{=OFFSET(A$1,SMALL(IF(LEN($D$2:$D$4)-LEN(SUBSTITUTE($D$2:$D$4,"/",))+1>=COLUMN($A:$I),{1;2; ...

感谢大神，非常厉害

袁勇