卓阳 发表于 2021-12-16 15:51:00

如何使用公式将单元格内的单词提取出来

如何使用公式将单元格内的单词提取出来。
如图：想把A列英语短句中的单词通过公式提取出来并且不能包含重复值。


小象在雾里丶 发表于 2021-12-23 04:47:16

=IFERROR(INDEX(TRIM(MID(SUBSTITUTE($A$2&" "&$A$3," ",REPT(" ",100)),(ROW($1:$7)-1)*100+1,100)),SMALL(IF(MATCH(TRIM(MID(SUBSTITUTE($A$2&" "&$A$3," ",REPT(" ",100)),(ROW($1:$7)-1)*100+1,100)),TRIM(MID(SUBSTITUTE($A$2&" "&$A$3," ",REPT(" ",100)),(ROW($1:$7)-1)*100+1,100)),)=ROW($1:$7),ROW($1:$7),99),ROW(A1))),"")数组，三键下拉

艾海 发表于 2021-12-31 22:15:14

我只能分两步走
先TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",,$A$2:$A$10)," ",REPT(" ",999)),ROW(A1)*999-998,999))提取 再去重

用户胡子金不换 发表于 2022-1-6 05:32:26


=UNIQUE(MID(TEXTJOIN(" ",1,$A$2:$A$10),FIND("@",SUBSTITUTE(" "&TEXTJOIN(" ",1,$A$2:$A$10)&""," ","@",ROW($1:$100))),FIND("@",SUBSTITUTE(" "&TEXTJOIN(" ",1,$A$2:$A$10)&""," ","@",ROW($1:$100)+1))-FIND("@",SUBSTITUTE(" "&TEXTJOIN(" ",1,$A$2:$A$10)&""," ","@",ROW($1:$100)))-1))

是不是这样的效果？

笏笙 发表于 2022-1-13 05:21:16

C2下拉
=FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(" ",,A$2:A$10)," ","</b><b>")&"</b></a>","//b["&ROW(A1)&"]")

365版本C2
=UNIQUE(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(" ",,A$2:A$10)," ","</b><b>")&"</b></a>","//b"))

南下十度 发表于 2022-1-17 06:10:42

用PQ拆分列→拆分为行→删除重复项

大圣鼠 发表于 2022-2-3 10:38:32

=TRIM(MID(SUBSTITUTE(CONCAT(A$2:A$10&" ")," ",REPT(" ",999)),999*SMALL(IF(MATCH(TRIM(MID(SUBSTITUTE(CONCAT(A$2:A$10&" ")," ",REPT(" ",999)),999*ROW($1:$99)-998,999)),TRIM(MID(SUBSTITUTE(CONCAT(A$2:A$10&" ")," ",REPT(" ",999)),999*ROW($1:$99)-998,999)),)=ROW($1:$99),ROW($1:$99),999),ROW(A1))-998,999))
数组三键下拉。

洋羊洋 发表于 2022-3-1 12:13:49

=TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",1,UNIQUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(" ",1,$A$2:$A$10)," ",REPT(" ",500)),ROW($1:$100)*500-499,500))))," ",REPT(" ",500)),ROW(A1)*500-499,500))三键结束 下拉 适用365版本

余国锦 发表于 2022-3-13 04:12:06

=FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(" ",,A2:A10)," ","</b><b>")&"</b></a>","//b")

冯杰 发表于 2022-3-17 20:35:07

Sub lll()

Dim rex As New VBScript_RegExp_55.RegExp
Dim rng As Range
Dim rng1 As Range
Dim a
Dim i As Integer

Set rng = Range("a2:a10")
For Each rng1 In rng
With rex
    .Global = True
    .IgnoreCase = True
    .Pattern = "+"
    For Each a In .Execute(rng1)
      i = i + 1
      Cells(i + 1, 3) = a
    Next
End With
Next
Columns("C:C").Select
ActiveSheet.Range("$C$1:$C$35").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub
页: [1]
查看完整版本: 如何使用公式将单元格内的单词提取出来