10组数据，但是我的公式只能采集到第5组。请帮忙修正。谢谢

lidongmax

又来请教了，首先致谢各位。
1、10组数据，但是我的公式只能采集到第5组。请帮忙修正。谢谢
2、图片内有具体描述

lidongmax

我使用的是Excel 2019，365版本有些函数应该是无法使用的。

lidongmax

很笨拙的一个公式=SUBSTITUTE(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",1000)),3*1000-999,1000)),"(商品数量",)&LEFT(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",1000)),4*1000-999,1000)),1)& IF(ISERROR(SEARCH("【2",$AC2)),"","+"&SUBSTITUTE(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",1000)),6*1000-999,1000)),"(商品数量",)&LEFT(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",1000)),7*1000-999,1000)),1))& IF(ISERROR(SEARCH("【3",AC2)),"","+"&SUBSTITUTE(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",1000)),9*1000-999,1000)),"(商品数量",)&LEFT(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",1000)),10*1000-999,1000)),1))& IF(ISERROR(SEARCH("【4",AC2)),"","+"&SUBSTITUTE(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",1000)),12*1000-999,1000)),"(商品数量",)&LEFT(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",1000)),13*1000-999,1000)),1))& IF(ISERROR(SEARCH("【5",AC2)),"","+"&SUBSTITUTE(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",1000)),15*1000-999,1000)),"(商品数量",)&LEFT(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",1000)),16*1000-999,1000)),1))& IF(ISERROR(SEARCH("【6",AC2)),"","+"&SUBSTITUTE(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",2000)),18*2000-1999,2000)),"(商品数量",)&LEFT(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",2000)),19*2000-1999,2000)),1))& IF(ISERROR(SEARCH("【7",AC2)),"","+"&SUBSTITUTE(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",2000)),21*2000-1999,2000)),"(商品数量",)&LEFT(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",2000)),22*2000-1999,2000)),1))& IF(ISERROR(SEARCH("【8",AC2)),"","+"&SUBSTITUTE(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",2000)),24*2000-1999,2000)),"(商品数量",)&LEFT(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",2000)),25*2000-1999,2000)),1))& IF(ISERROR(SEARCH("【9",AC2)),"","+"&SUBSTITUTE(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",2000)),27*2000-1999,2000)),"(商品数量",)&LEFT(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",2000)),28*2000-1999,2000)),1))& IFERROR(SEARCH("【10",AC2)&SUBSTITUTE(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",2000)),30*2000-1999,2000)),"(商品数量",)&LEFT(TRIM(MID(SUBSTITUTE(AC2,":",REPT(" ",2000)),31*2000-1999,2000)),1),)

hxj119

高版本效果：
=TEXTJOIN(CHAR(10),,REGEXP(AC2,"(?<=数量:)\d+")&"件，"&REGEXP(AC2,"(?<=Color:).+盒(?=\))"))

leepoison

为什么 第一个 不是   1+A1无盒）

leepoison

这样子的吗？

ww87725244

1. Function aa(rng As Range)
   
2. Dim d, s, k, m, p
   
3. Set d = CreateObject("vbscript.regexp")
   
4. With d
   
5.   .Global = True
   
6.   .Pattern = "[A-Z0-9].+[盒]|\d{1,}[ 件]"
   
7.   Set k = .Execute(rng.Value)
   
8.   For Each s In k
   
9.     If InStr(s, "Color:") > 0 Then
   
10.       m = Replace(s, "Color:", "")
    
11.     Else
    
12.        m = m & "+" & s
    
13.        p = p & m & "■"
    
14.     End If
    
15.   Next
    
16. End With
    
17. aa = p
    
18. End Function

复制代码

solextrade

=TEXTJOIN(CHAR(10),,--MID(AC3,SMALL(IF(MID(AC3,ROW(1:1999),2)="量:",ROW(3:2001)),ROW(1:10)),2)&"+"&TRIM(LEFT(SUBSTITUTE(MID(AC3,SMALL(IF(MID(AC3,ROW(1:1999),2)="r:",ROW(3:2001)),ROW(1:10)),9),CHAR(10),REPT(" ",19)),19)))

lidongmax

leepoison 发表于 2024-7-17 23:24
这样子的吗？

看样子是的，能给公式验证吗

lidongmax

备用...................
.