提取字符串中分隔符值左边的两个单词

This question is not a duplicate to a previously posted Excel question, instead this question is seeking to Extract Words LEFT of the Delimiter; whereas the previously posted question Extracts words to the Right of Delimiter.

这个问题不是先前发布的Excel问题的副本，而是要提取分隔符后面的单词;而前面提到的问题则是在分隔符的右边提取单词。

Using MS Excel, I would like to extract two words (three spaces) to the LEFT of delimiter value "^", including extracting the identifier word with attached/associated with delimiter "^"

使用Excel女士,我想提取两个词(三个空格)左边的分隔符值“^”,包括提取的相关词附加/标识符分隔符“^”

EXAMPLE: Cell A2

例如:细胞A2

Johnny and I were planning on going to the movie to see ^Batman Returns, but it was to late.

约翰尼和我打算去看电影看到^《蝙蝠侠归来》,但它是迟了。

Results: Cell B2

结果:细胞B2

to see ^Batman

看到^蝙蝠侠

2 个解决方案

#1

There may be some better solution but here is what i have:

或许有更好的解决办法，但我有以下几点:

=MID(LEFT(A1,FIND(" ",A1;FIND("^",A1))-1),FIND("^",SUBSTITUTE(A1," ","^",LEN(LEFT(A1,FIND("^",A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND("^",A1))," ",""))-2))+1,9999)

the core is : LEN(LEFT(A1,FIND("^",A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND("^",A1))," ",""))
it counts the spaces from string start till your delimeter, then replacing the count - 2 space# with anoter delimeter to find your start FIND("^",SUBSTITUTE(A1," ","^",LEN(...)-LEN(...)-2))+1 doing this for a string that ends earlier LEFT(A1,FIND(" ",A1;FIND("^",A1))-1) (you dont need to do the long term a second time)

核心是:LEN(左(A1,发现(“^”,A1)))LEN(替代(左(A1,发现(“^”,A1))," "," "))计算空间从字符串开始直到你的字段,然后计数- 2空间#替换为另一字段名找到你开始找到(“^”,替代(A1,“”,“^”,LEN(…)LEN(…)2))+ 1做了一个字符串,该字符串结束之前离开(A1,发现(A1”;发现(“^”,A1))1)(不需要做长期的第二次)

#2

A non-VBA formula approach is possible (possibly as an array formula) but would be like making a sculpture with a butter knife. Here is a VBA function which can be used as a UDF:

一个非vba公式的方法是可能的(可能是一个数组公式)，但就像用黄油刀做一个雕塑。这里有一个VBA函数，可以用作UDF:

Function ExtractLeft(str As String, delim As String, words As Long) As String
    Dim i As Long, n As Long
    Dim A As Variant
    Dim left_words As String, right_word As String

    A = Split(str, delim)
    right_word = A(1)
    right_word = Split(right_word)(0)
    right_word = delim & right_word
    left_words = A(0)
    A = Split(Trim(left_words))
    n = UBound(A)
    For i = n To n - words + 1 Step -1
        right_word = A(i) & " " & right_word
    Next i
    ExtractLeft = right_word
End Function

The advantage of the VBA approach is that you can easily change both the delimiter and the number of words to extract:

VBA方法的优点是可以轻松地更改分隔符和要提取的单词数量:

#1