正则函数表达式是否能实现数据的转换

齐而斋

模板里面“后台数据”标是网店导出来的数据，像转化成“实现功能”表的样式，使用正则函数表达式是否可行，求大佬指点

nie414006

这种类型还是vba好些

nie414006

nie414006

仅对例子

无关岁月

1. =IFNA(REGEXEXTRACT(后台数据!C2,{"短袖.*?\.\K\d+","短袖.*?:\K\d+","长裤.*?\.\K\d+","长裤.*?:\K\d+","套装.*?\.\K\d+","套装.*?:\K\d+"}),"")

复制代码

wp231957

这样可以不
=LET(a,FILTER(A2:C7,ISNUMBER(FIND("短袖",C2:C7))),b,FILTER(A2:C7,ISNUMBER(FIND("长裤",C2:C7))),c,FILTER(A2:C7,ISNUMBER(FIND("套装",C2:C7))),d,HSTACK(CHOOSECOLS(a,1,2),REGEXP(CHOOSECOLS(a,3),"\.\K\d+"),REGEXP(CHOOSECOLS(a,3),":\K\d+"),IFNA(EXPAND("短袖",ROWS(a)),"短袖")),e,HSTACK(CHOOSECOLS(b,1,2),REGEXP(CHOOSECOLS(b,3),"\.\K\d+"),REGEXP(CHOOSECOLS(b,3),":\K\d+"),IFNA(EXPAND("长裤",ROWS(b)),"长裤")),f,HSTACK(CHOOSECOLS(c,1,2),REGEXP(CHOOSECOLS(c,3),"\.\K\d+"),REGEXP(CHOOSECOLS(c,3),":\K\d+"),IFNA(EXPAND("套装",ROWS(c)),"套装")),VSTACK({"下单时间","收货人","码长","数量","类别"},d,e,f))

龙逸凡

C3:
=IFNA(REGEXP(后台数据!$C2,C$1&".*?\K\d+"),"")

D3:
=IFNA(REGEXP(后台数据!$C2,C$1&".*?购买数量:\K\d+"),"")

高冷cool

1. Sub day1092_1()
   
2.     Set a = Worksheets(1)
   
3.     Set b = Worksheets(3)
   
4.     x = a.UsedRange.Rows.Count
   
5.     Set regx = CreateObject("vbscript.regexp")
   
6.     regx.Global = True
   
7.     regx.Pattern = "(短袖|长裤|套装).*?）\.(\d+).*?\[购买数量:(\d+)\]"
   
8.     Set d = CreateObject("scripting.dictionary")
   
9.     y = b.UsedRange.Columns.Count
   
10.     For i = 3 To y Step 2
    
11.         d(b.Cells(1, i)) = i
    
12.     Next
    
13.     For i = 2 To x
    
14.         b.Cells(i + 1, 1) = a.Cells(i, 1)
    
15.         b.Cells(i + 1, 2) = a.Cells(i, 2)
    
16.         Set m = regx.Execute(a.Cells(i, 3))
    
17.         For Each mm In m
    
18.             kind = mm.submatches(0)
    
19.             If kind = "短袖" Then
    
20.                 b.Cells(i + 1, 3) = mm.submatches(1)
    
21.                 b.Cells(i + 1, 4) = mm.submatches(2)
    
22.             ElseIf kind = "长裤" Then
    
23.                 b.Cells(i + 1, 5) = mm.submatches(1)
    
24.                 b.Cells(i + 1, 6) = mm.submatches(2)
    
25.             ElseIf kind = "套装" Then
    
26.                 b.Cells(i + 1, 7) = mm.submatches(1)
    
27.                 b.Cells(i + 1, 8) = mm.submatches(2)
    
28.             End If
    
29.         Next
    
30.     Next
    
31. End Sub

复制代码

针对楼主给出的例子给出的代码，运行结果在sheet3表中

齐而斋

无关岁月 发表于 2024-10-8 14:09

感谢指点，完美解决了

齐而斋

龙逸凡 发表于 2024-10-9 07:29
C3:
=IFNA(REGEXP(后台数据!$C2,C$1&".*?\K\d+"),"")

感谢大佬指点