无法设计出一个公式来执行以下操作...任何帮助都值得赞赏
我有一个文本单元格,其中包含未知数量的双引号单词。我需要提取引用的单词,然后在另一个单元格中列出(在新行中或用逗号/分号连接)
示例文本:当我看向“别处”时,我的“狗”吃了我的“苹果”
要求输出:狗、苹果、离开
答案1
根据您的 Excel 版本,
对于单独单元格中的条目:
=SUBSTITUTE(FILTERXML("<t><s>" & SUBSTITUTE(A1," ","</s><s>") & "</s></t>","//s[contains(.,'""')]"),"""","")
对于同一单元格中的条目:
=TEXTJOIN("; ",,SUBSTITUTE(FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s[contains(.,'""')]"),"""",""))
该公式只是在空格分隔的单词中查找单引号、双引号。如果这还不够具体,可以创建更复杂的公式,但使用正则表达式的 VBA 可能更容易实现。
答案2
尝试以下用户定义函数:
Public Function DontQuoteMe(s As String) As String
Dim q As String, arr, i As Long, wf As WorksheetFunction
Dim hr As String
Set wf = Application.WorksheetFunction
q = Chr(34)
hr = Chr(10)
DontQuoteMe = ""
If InStr(s, q) = 0 Then Exit Function
arr = Split(" " & s & " ", q)
i = 1
For Each a In arr
If wf.IsEven(i) Then
DontQuoteMe = DontQuoteMe & hr & a
End If
i = i + 1
Next a
DontQuoteMe = Mid(DontQuoteMe, 2)
End Function