Excel从字符串中提取第n个第一个单词

I'd like to create a function in vba to extract the first nth words from a string and to look like this

我想在vba中创建一个函数来从字符串中提取第n个单词并看起来像这样

ExtractWords(affected_text, delimiter, number_of_words_to_extract)

ExtractWords(affected_text,delimiter,number_of_words_to_extract)

I tried a solution but it only extracts the first two words.

我试过一个解决方案,但它只提取前两个单词。

Function FirstWords(myStr As Variant, delimiter,words_to_extract) As Variant
  FirstWords = Left(myStr, InStr(InStr(1, myStr, delimiter) + 1, myStr, delimiter, vbTextCompare) - 1)
End Function

Any ideas? Thanks

有任何想法吗?谢谢

2 个解决方案

#1

Use Split() function. It returns array of String, split using the delimiter and limit of words you specify.

使用Split()函数。它返回String数组,使用分隔符和您指定的单词限制进行拆分。

Dim Result As Variant
Result = Split("Alice,Bob,Chuck,Dave", ",") 'Result: {"Alice,"Bob","Chuck","Dave"}
Result = Split("Alice,Bob,Chuck,Dave", ",", 2) 'Result: {"Alice,"Bob"}

#2

@Taosique's answer using Split is excellent, but if you want the result returned as a string you can do the following:

@Taosique使用Split的答案非常好,但如果你想将结果作为字符串返回,你可以执行以下操作:

Function FirstWords(myStr As String, delimiter As String, words_to_extract As Long) As Variant
    Dim i As Long, k As Long
    For i = 1 To Len(myStr)
        If Mid(myStr, i, 1) = delimiter Then
            k = k + 1
            If k = words_to_extract Then
                FirstWords = Mid(myStr, 1, i)
                Exit Function
            End If
        End If
    Next I

    'if you get to here -- trouble
    'unless the delimiter count is words_to_extract - 1
    If k = words_to_extract - 1 Then
        FirstWords = myStr
    Else
        FirstWords = CVErr(xlErrValue)
    End If    End Function

Sub test()
    Debug.Print FirstWords("This is a test. I hope it works", " ", 4)
    Debug.Print FirstWords("This is a test. I hope it works", " ", 10)
End Sub

When test is run it first displays the string "This is a test." then prints an error condition.

当测试运行时,它首先显示字符串“This is a test”。然后打印错误条件。

Much the same effect as the above can be achieved by first splitting the string using Split and then rejoining it using Join. A subtle difference is the behavior if there are less than words_to_extract words. The Split then Join approach will return the whole string. The above code treats this as an error condition and, if used as a UDF worksheet function, will display #VALUE! in any cell that contains it.

通过首先使用Split拆分字符串然后使用Join重新加入它,可以实现与上述相同的效果。如果少于words_to_extract字,则行为的细微差别。 Split then Join方法将返回整个字符串。上面的代码将此视为错误条件,如果用作UDF工作表函数,将显示#VALUE!在任何包含它的单元格中。

#1