How can I Extract Characters between two words in excel but search from Right to Left

Question

Currently I have this in Cell A1:

"timeZone":"Etc/UTC"},"body":"[~kevin.tom] [~sergio.Melle] [~seema.item] Was the report delivered to the client?","updateAuthor":"body":"[~katelyn.robert]\r\n\r\nNot yet","updateAuthor":

And I want to extract everything between

"body":"

and

","updateAuthor

As you can see in A1, if I split the content between "body":" and ","updateAuthor we have two results:

[~kevin.tom] [~sergio.Melle] [~seema.item] Was the report delivered to the client?

and

[~katelyn.robert]\r\n\r\nNot yet","updateAuthor

I want to be able to extract the last result, which is

[~katelyn.robert]\r\n\r\nNot yet","updateAuthor

I will be always be adding more to cell A1 and I need Cell B1 to update and pull the last result, whatever it may be.

Currently, I was able to come up with this code in Cell B1:

=MID(B1, FIND("body",B1)+7, FIND("updateAuthor", B1, FIND("body", B1)+1)-FIND("body",B1)-10)

This works for me but it extracts the first result it finds whereas I would like the results of the last occurrence. Essentially, excel searched from Left to Right when I would like it to search from Right to Left. Does anyone know how to modify this to make it work?

Currently, with my code in B1, I am getting this in B1:

[~kevin.tom] [~sergio.Melle] [~seema.item] Was the report delivered to the client?

But I want this:

[~katelyn.robert]\r\n\r\nNot yet

I am trying to accomplish this without using VBA

Answer 1

OK, I just want to put out there that I really do believe this would be easier to figure out with VBA, but I was curious enough to do it as a formula.

=MID(A1,FIND("body",A1,FIND("body",A1,1)+1)+7,FIND("updateAuthor",A1,FIND("updateAuthor",A1,1)+1)-(FIND("body",A1,FIND("body",A1,1)+1)+10))

This assumes that you can use "body" and "updateAuthor" as you did in the formula you provided. If you really want to look for the character strings with the punctuation symbols, such as "body"." then the find would need something like CHAR(34) & "body" & CHAR(34) & ":" & CHAR(34) and CHAR(34) & "," & CHAR(34) & "updateAuthor". If you do that, you will also have to change the offset numbers that are added (+7 and +10).

This also assumes that there are two, and only two, authors in the cell. This approach will not work if you have a variable number of authors.

The syntax for the Mid function is MID(text,start_num,num_chars).

The syntax for the Find function is FIND(find_text,within_text,start_num).

FIND("body",A1,FIND("body",A1,1)+1)+7

This gives you the starting number for the Mid function.

The inside find FIND("body",A1,1) returns the location of the first occurrence of "body" in A1. You need to add 1 to that for the starting number of the next find or you will just get the same location. The outside find then looks at A1 again for "body", but starts looking one character after the location of the first occurrence. You have to add an offset to find the location of the first character after your "body":" because the find function will tell you where the b is.

FIND("updateAuthor",A1,FIND("updateAuthor",A1,1)+1)-(FIND("body",A1,FIND("body",A1,1)+1)+10

This gives you the number of characters for the Mid function.

These recursive find statements work the same as above to find the difference between the start of the author's information and the start of the end tag, ","updateAuthor, with the appropriate offset added at the end.

Answer 2

If you re-consider and will accept VBA, then consider the following User Defined Function:

Public Function GrabData(s As String) As String
    Dim dq As String
    dq = Chr(34)
    arr = Split(s, "body" & dq & ":" & dq)
    brr = Split(arr(UBound(arr)), dq & "," & dq & "updateAuthor")
    GrabData = brr(0)
End Function