extract text from a string that is between " " starting from the right

[cpowers]

I have a field with workflow history. I need the status at the end of the sentence which is in " " to be seperated to it's own column. There are other lines in the history with items in " " that I do not need to bring over. Only the furthest to the right status in " " when changed status or set status is mentioned. (do not need file upload or change in due date). Example below - see attached for it in excel. Thanks - 1st post.

Have	Desired Results
Jane Doe has changed status from "Assigned" to "Not Submitted"	Not Submitted
Jane Doe has set status to "Assigned"	Assigned
Jane Doe has uploaded the file "LI7130a_12_12_17.comp.pdf"
Jane Doe has changed status from "Not Submitted" to "Submitted"	Submitted
Jane Doe has set the due date of status "In Review" to 12/15/2017
Jane Doe has assigned responsibility to John Doe.
Jane Doe has changed status from "Submitted" to "In Review"	In Review
Jane Doe has transferred responsibility from Jane to John.
Jane Doe has changed status from "In Review" to "Review Completed"	Review Completed
Jane Doe has changed status from "Review Completed" to "Audit Form In Progress"	Audit Form in Progress
Jane Doe has changed status from "Audit Form In Progress" to "Audit Form Completed"	Audit Form Completed
Jane Doe has changed status from "Audit Form Completed" to "Approved with Contingencies"	Approved with Contingencies
Jane Doe has changed status from "Approved with Contingencies" to "Final Needed"	Final Needed
202012
Jane Doe has changed status from "Final Needed" to "Final Submitted"	Final Submitted
Jane Doe has set the due date of status "In Review" to 2/12/2018
Jane Doe has uploaded the file "223203 LI7130a_1_25_18.comp.pdf"
Jane Doe has changed status from "Final Submitted" to "Final Accepted"	Final Accepted

[MarqueeCrew]

@cpowers,

Are you going to Inspire?  Have you participated in the weekly challenges? Are you having fun?

Here's how I solved your challenge.  I struggled with using/searching for " signs.  I looked up the ascii hex code and wrote expressions searching for \x22.  Now I know that trick.  Next I check each field value to see if it matches the pattern of having stuff with status and quotes.  I then wrote a pattern that outputs the last quote contents to a field.

IIF(REGEX_Match([Have], ".*Status.*\x22.*\x22.*"),
REGEX_Replace([Have], ".*Status.*\x22(.*?)\x22.*", '$1'),'')

Please try this out and see if it works to your satisfaction.  If you have questions about it, please let me know.  I'm due outside to mow the lawn now.

Cheers,

Mark

[david_fetters]

@MarqueeCrew That's a great approach, I was scratching my head about the regex in this case.

[cpowers]

@MarqueeCrew thank you for the response. I am having trouble applying your recommendation (very new to the tools) - could you provide more details or a snapshot. Much appreciated!

[MarqueeCrew · Accepted solution]

@cpowers,

I've updated the formula (saw requirements that I had skipped about "Set" and "Changed" status.

IIF(Contains([Have], "changed Status") or Contains([Have], "set status"),
REGEX_Replace([Have], ".*Status.*\x22(.*?)\x22.*", '$1'),Null())

I also created a video for you as well as a short workflow (You'll need to reconnect the input to your file as I was lazy).

Cheers,

Mark

[cpowers]

@MarqueeCrew Thank you very much. This definitely works for the sample I provided and hopefully I don't run into any snags further down in the data set.

[JayZi]

Hi Everyone!

For those who would like to train usage of RegEx tool, please find my solution attached.

Regards,

JDZ

[2704ARR]

Hi, for some reason im running into this issue.

I am trying to extract everything after the first speech mark however it only gives me the last word in speech marks.

I would have used substring however the issue is that the first word will not always be the same. The only consistency that I have is I want everything after the first speech marks.

1.Example input:

hello "apple" and the "tree" and then drop the "oranges"

1. Required output: 

apple" and the "tree" and then drop the "oranges"

2 .Example input:

placeword "apple" and the "tree" and then drop the "oranges"

2. Required output: 

apple" and the "tree" and then drop the "oranges"