How to get a value after a character, from a string based on a text match using formula?-CodePudding

I got the following value:

tradicional;cropped$9$10;mullet$5$7

In cell A1, I can choose between tradicional, cropped and mullet. In cell A2, I pick 1, or 2.

If I pick cropped and 2, the value to be returned would be 10.

If I pick mullet and 1, the value to be returned would be 5. If

I'd go for len and left, but I don't see how this is going to work using the matching criteria.

Here's a practical example: https://docs.google.com/spreadsheets/d/1dFzXmtKj15EzApTKUKv8yF7_mAIB1COPSgMLMMmFE4E/edit?usp=sharing

Appreciate your help.

CodePudding user response：

Description

You can split the text string on semicolon ";" into 3 parts. The depending on which part you choose, you can split it on dollar sign "$" then you can get the "item" and return an integer. I leave it to you to figure out how to incorporate into your script.

Script (Test Case)

function makeAChoice() {
  try {
    console.log("You chose " getChoice("cropped",2));
    console.log("You chose " getChoice("mullet",1));
    console.log("You chose " getChoice("somethingelse",1));
  }
  catch(err) {
    console.log(err);
  }
}

function getChoice(choice,item) {
  try {
    var text = "tradicional;cropped$9$10;mullet$5$7";
    text = text.split(";");
    text = text.filter( s => s.includes(choice) );
    if( text.length < 1 ) throw "Error choice [" choice "] not found!";
    text = text[0].split("$");
    return parseInt(text[item]);
  }
  catch(err) {
    console.log(err);
  }
}

Console.log

8:32:38 AM  Notice  Execution started
8:32:38 AM  Info    You chose 10
8:32:38 AM  Info    You chose 5
8:32:38 AM  Info    Error choice [somethingelse] not found!
8:32:38 AM  Info    You chose undefined
8:32:38 AM  Notice  Execution completed

Reference

CodePudding user response：

Per my comments to your original post, I feel that there is a lot we don't know about your bigger goal. But as you aren't able to provide that, this solution will work for your one exact example.

Place the following formula in C4:

=ArrayFormula(IFERROR(VLOOKUP(A4;SPLIT(FLATTEN(SPLIT(E4;";"));"$");B4 1;FALSE)))

(See the new sheet "Erik Help.")

The inner SPLIT splits the E4 string at every semicolon.

FLATTEN sends that all to one column.

The outer SPLIT then splits at each "$".

VLOOKUP can then try to find the Col-A term in the first column of the resulting virtual chart. If found, it will return the column value that matches the Col-B value 1 (since column 1 of the virtual array is the labels, e.g., 'tradicional,' etc.).

If no match is found for both the Col-A and Col-B data, then IFERROR returns null.