Excel-VBA，如何从几行句子中提取第 n 个单词
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8757899/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel-VBA, How do i extract nth word from some rows of sentences
提问by Kwai Lum
How do i extract nth word from some rows of sentences? For example, i have a sentence in column A1, and another in A2, A3 and so on, How do i extract 2nd word from each sentence? And display in a cell beside each sentence. Appreciated if someone can write an example for this, i'm new to excel-vba.
如何从几行句子中提取第 n 个单词？例如，我在 A1 列中有一个句子，在 A2、A3 等中有另一个句子，我如何从每个句子中提取第二个单词？并显示在每个句子旁边的单元格中。感谢有人可以为此编写一个示例，我是 excel-vba 的新手。
回答by Alex K.
Split it;
拆分它；
'//get value
dim para As string: para = range("a1").value
'//normalize new lines to space & split on space
dim words() As string
words = Split(Replace$(para, vbCrLf, " "), " ")
'//3rd word in adjacent cell;
range("b1").value=words(2)
回答by jgallant
You can do this easily in VBA, but I am assuming you want to do this with Formulas.
您可以在 VBA 中轻松完成此操作，但我假设您想使用公式来完成此操作。
Here is how you can break this down. Consider cell A1 as having the string you want to extract the second word from. You can use the Find function to determine where the second word starts from:
以下是您如何分解它。将单元格 A1 视为包含要从中提取第二个单词的字符串。您可以使用 Find 函数来确定第二个单词的起始位置：
=FIND(" ", A1, 1)
Same function can be used to find out where the second word finishes:
可以使用相同的函数找出第二个单词的结束位置：
=FIND(" ",A1, FIND(" ",A1)+1)
Now, we can use the Mid function in order to extract the word from its start and end positions:
现在，我们可以使用 Mid 函数从开始和结束位置提取单词：
=MID(A1, FIND(" ", A1, 1), FIND(" ",A1, FIND(" ",A1)+1)-FIND(" ", A1, 1))
This final formula is what you want to use. It looks complicated, but it is just the first two formulas copied into the Mid function.
这个最终公式就是您要使用的公式。它看起来很复杂，但它只是复制到 Mid 函数中的前两个公式。
I hope this helps.
我希望这有帮助。
回答by wayl0n
You can paste this into a VBA module for a UDF, only works for a single cell though. Just give it a cell reference and which word number you want it to extract:
您可以将其粘贴到 UDF 的 VBA 模块中，但仅适用于单个单元格。只需给它一个单元格引用以及您希望它提取的单词编号：
Function WordExtract(CellRef As Range, Wordnum As Integer)
Dim StartPos As Integer, EndPos As Integer, Counter As Integer
StartPos = 1
For i = 1 To Len(CellRef)
If Mid(CellRef, i, 1) = " " Then
If Wordnum - Counter = 1 Then
EndPos = i - StartPos
Exit For
Else:
StartPos = i + 1
Counter = Counter + 1
End If
End If
Next i
If EndPos = 0 Then EndPos = Len(CellRef)
WordExtract = Mid(CellRef, StartPos, EndPos)
End Function
