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

卓阳

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


求助文件.zip (7.93 KB, 下载次数: 15)

2021-12-16 15:51 上传

点击文件名下载附件
求助文件.zip

小象在雾里丶

=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))),"")数组，三键下拉

艾海

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

用户胡子金不换

=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))

是不是这样的效果？
求助文件.zip (8.47 KB, 下载次数: 161)

2021-12-16 15:51 上传

点击文件名下载附件
求助文件.zip

笏笙

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

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

南下十度

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

大圣鼠

=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))
数组三键下拉。

洋羊洋

=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版本

余国锦

=FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(" ",,A2:A10)," ","</b><b>")&"</b></a>","//b[not(preceding::*=.)]")

冯杰

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 = "[a-z']+"
    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