0

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

  • I assume that you do not have a formula in Cell B1 that is referencing B1.  I guess you have a formula in Cell B1 that is referencing A1 (or vice versa).  Please do not respond in comments; edit your question to make it clearer and more complete. – Scott Jul 9 '18 at 18:43
  • Will there always be two instances of "body":"? – cybernetic.nomad Jul 9 '18 at 18:44
  • This is a growing cell so more texts will be added to it. But when texts are added to it, the desired text will always be between "body":" and ","updateAuthor So to answer your question, yes, there will be multiple "body":" – Kevin Truong Jul 9 '18 at 18:58
  • You could do it easily enough with a regular expression, something like ("body":")(.+?)(","updateAuthor":$). More info on how to do so here – cybernetic.nomad Jul 9 '18 at 19:24
  • Thanks for the feedback, I really appreciate it. I am looking for a way to do this without VBA. Is there something I can add to the formula to accomplish this? – Kevin Truong Jul 9 '18 at 20:02
0

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.

| improve this answer | |
0

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

enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.