多行转多列

hxj119

1. =DROP(REDUCE("",A2:A5,LAMBDA(x,y,IFNA(HSTACK(x,TOCOL(REGEXP(y,"[一-龟]+"))),""))),,1)

复制代码

jerrike

1. =IFNA(TRANSPOSE(TEXTSPLIT(REGEXP(TEXTJOIN(";",,A2:A5),"[\s\d.%]",2),",",";")),"")

复制代码

袁勇

低版本：

1. =TRIM(MID(SUBSTITUTE(SUBSTITUTE(OFFSET($A$1,COLUMN(A1),)," "," ,"),",",REPT(" ",200)),(ROW(A1)*3-2)*200-199,200))

复制代码

boy8199

https://club.excelhome.net/thread-1577442-1-1.html

zhangcheng6688

借楼学习一下正则表达式，打扰了
Sub test()
lastr = Cells(Rows.Count, "a").End(xlUp).Row
arr = Range("a1:a" & lastr)
ReDim brr(1 To 1000, 1 To UBound(arr))
Set reg = CreateObject("vbscript.regexp")
With reg
   .Global = True
   .Pattern = "[一-龟]+"
End With
c = 1
For i = 2 To UBound(arr)
   If reg.test(arr(i, 1)) Then
      Set mh = reg.Execute(arr(i, 1))
      If mh.Count > 0 Then
         For Each mho In mh
            m = m + 1
            brr(m, c) = mho
         Next
      End If
   End If
   If m > Mx Then
      Mx = m
   End If
   m = 0
   c = c + 1
Next
[b2].Resize(Mx, c) = brr
End Sub

deadzlq

PQ参考解法

mykitami

=MAP(IFNA(TRANSPOSE(TEXTSPLIT(TEXTJOIN(";",,A2:A5),",",";",1)),""),LAMBDA(x,TEXTBEFORE(x," ",,,,"")))

mjm04

WPS最新版 可用，一步到位

=DROP(IFNA(REDUCE("",A2:A5,LAMBDA(a,b,HSTACK(a,TOCOL(REGEXP(b,"[一-龢]+"),3)))),""),,1)

mjm04

无关岁月 发表于 2025-3-18 16:53

REGEXP  第3参数-3 来完整提取，用REDUCE  语法糖形式   进行暴力堆叠~~~~~~~~~~~~~~~


好简洁的思路呀~~~

wyslw2004

=TRANSPOSE(DROP(IFNA(REDUCE("",A2:A5,LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,CHAR(ROW($10:$128)),,1)))),""),1))