excel - VBA 公式在第二个空格后删除所有内容

Question

我试图从 D 列复制到 E 列每行的前两个单词，但仍然找不到错误在哪里....

Range("E1:E" & lLastRow).Formula = "=LEFT(D1,FIND("" "",D1,FIND("" "",D1)+1)-1)"

Answer 1

另一种选择，而不是使用公式，您可以使用 Split功能。

代码

Dim i As Long, LastRow As Long
Dim WordsArr As Variant

' loop through rows
For i = 1 To LastRow
    WordsArr = Split(Range("D" & i).Value, " ")  ' use Split and space to read cell words to array

    If UBound(WordsArr) >= 1 Then ' make sure the cell contents is at least 2 words
        Range("E" & i).Value = WordsArr(0) & " " & WordsArr(1) ' insert only the first 2 words

    Else ' in case there are less than 2 words
        ' do someting....

    End If
Next i

End Sub