CODEWITHSUNDEEP
excelextractformula
Miaka3
Miaka3

Reputation: 405

Extract text two words to the LEFT of Delimiter value within string

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.

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 "^"

EXAMPLE: Cell A2

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

Results: Cell B2

to see ^Batman

Upvotes: 1

Views: 410

Answers (2)

Dirk Reichel
Dirk Reichel

Reputation: 7979

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)

Upvotes: 1

John Coleman
John Coleman

Reputation: 51998

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:

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:

enter image description here

Upvotes: 0

Related Questions