Parse data between specific characters

[jpaplanus]

Hello community.

I am trying to parse data from a string. Based on the structure of the string I am almost certain that RegEx would be the solution, but admittedly I might as well be trying to read a symbol-based language when it comes to RegEx. I struggle to understand the basics of it. Below is an example of one string that is contained in a single cell. In some cases the field may only have one value I am trying to get, while others, like the below, will have multiple. The data is coming out of a SharePoint list. I am looking to grab the text after "Value", so in the below I am trying to get my parsed results column to be Middle,National,Technical,Finance. This column could have as many as 11 values I am trying to parse out so I want to be sure I can catch all of them.

[{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":5,"Value":"Middle"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":6,"Value":"National"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":7,"Value":"Technical"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":3,"Value":"Finance"}]

Any help is greatly appreciated! I am also very open to a solution that does not involve RegEx as it would be easier for me to replicate in the future with some other dataset. 

[Felipe_Ribeir0 · Accepted solution]

Hi @jpaplanus 

Try this formula

REGEX_Replace([Input], '(.*?Value"\:")(.*?)("\})(\,?)(]?.*?)', '$2$4')

[jdminton]

Here is a non-regex solution for your issue

 

 

 

 

[caltang]

Combining both @Felipe_Ribeir0 and @jdminton 's solutions, you get this:

Here's the REGEX Parse:

"Value":"(.*?)"

If the above solved your need, kindly like & mark as accepted solution so that you may help others find the solution more quickly + to close the thread as is. Thanks!

Best regards,
Calvin Tang
https://www.linkedin.com/in/calvintangkw/

[jpaplanus]

Thank you @Felipe_Ribeir0 for the solution. This worked perfectly! Someday I will try and fully understand all of the elements of the Regex formula. 

Thank you @jdminton for the suggestion. Your idea almost worked. I think my pasted text of the field may have been misleading. It is all one string field, not four different rows.

[jrgo · Accepted solution]

Here's an alternative option that doesn't require regex. Being that the string field is JSON formatted, you can use the JSON Parse tool to extract the objects values.

[jdminton]

@jpaplanus yes, I started it as one string. If you want the results combined together, add a summarize tool at the end to concatenate. You can put a record ID tool in the beginning to track the string throughout the process. group by on the record ID and concatenate to get the results for more than one string.

[jpaplanus]

Thanks @jrgo, I did not even think of this. A great solution to this particular situation!