UDF that extracts specific text from cell based on user input string

[Tunesmith]

Hi,

Looking to create a UDF that extracts a specific portion of a cell based on a input from the user.

Example data:

ModelResults

[{"name":"PMML.Model1","outputs":{"Predicted_decision":"APPROVE","Probability_APPROVE":"0.5","Probability_REJECT":"0.5"}},{"name":"PMML.Model2","outputs":{"Predicted_decision":"APPROVE","Probability_APPROVE":"1.0","Probability_REJECT":"0.0"}},{"name":"PMML.Model3","outputs":{"Predicted_decision":"APPROVE","Probability_APPROVE":"0.94","Probability_REJECT":"0.06"}},{"name":"PMML.Model4","outputs":{"Predicted_decision":"REJECT","Probability_APPROVE":"0.16613686480221865","Probability_REJECT":"0.8338631351977813"}}]

The column is a named range called "ModelResults" and it contains a series of outputs from several models, using made up names "PMML.Model1", "PMML.Model2" etc., the files may be 1000s of rows long.
Each model output is contained inside { } brackets, separated by commas.
There may be different models per row or some that aren't always present but the { } delimiter and the "PMML.####" name is consistent.

I currently use a nested FIND formula to extract the part that I'm after between the { } brackets.
Example using "Model3":

Please Login or Register  to view this content.

Model3 extracted with formula

{"name":"PMML.Model3","outputs":{"Predicted_decision":"APPROVE","Probability_APPROVE":"0.94","Probability_REJECT":"0.06"}}

My desired function: To be able to type e.g. =ModelOutput(Model3) in a cell and return the corresponding output directly instead of using the formula. It could return blank or "N/A" if the model output doesn't exist. Is this possible?

I've attached a dummy spreadsheet.

[Norie]

If it's a UDF you want you'll need 2 inputs, one the model you are looking for and one the cell to check.

Something like this.

Please Login or Register  to view this content.

Which could be used like this.

=ModelOutput("Model3",B2)

[Marc L]

Hi !

So you want an UDF which performs the same than your formula and maybe in a slower way, right ?

In this case the formula in C2 cell will be =ModelOutput(B2,"Model3") …

Edit : too late …

[Tunesmith]

If it's a UDF you want you'll need 2 inputs, one the model you are looking for and one the cell to check.

Something like this.

Please Login or Register  to view this content.

Which could be used like this.

=ModelOutput("Model3",B2)

Hi !

So you want an UDF which performs the same than your formula and maybe in a slower way, right ?

Edit : too late …

A slower calculation speed isn't necessarily an issue for the use case so this solution works great, thank you both!