Formular to Extract Numbers out of a string

[junaid2ali]

I have a column 'Original' with the value: Breakfast-NO|Lunch-YES|Dinner-YES

I want to the next three columns that are set to formula to automatically be set like this:

Original	Breakfast	Lunch	Dinner
Breakfast-NO|Lunch-YES|Dinner-YES	FALSE	TRUE	TRUE

Breakfast would be the string between the first '-' and '|'
Lunch would be the string between the second '-' and '|'
Dinner would be the string between the third '-' and '|'

also want to convert the value from No to FALSE, YES to True

Thank you.

[pressGO_design · Accepted solution]

I imagine this could be done only using a REGEX_EXTRACT and IF formula, but REGEX and I aren't BFFs yet, so here's my workaround:

Breakfast Formula: IF(MID(REGEX_EXTRACT(Original, "fast-.*"), 6, 1)="N", "FALSE", "TRUE")

Lunch Formula: IF(MID(REGEX_EXTRACT(Original, "unch-.*"), 6, 1)="N", "FALSE", "TRUE")

Dinner Formula: IF(MID(REGEX_EXTRACT(Original, "nner-.*"), 6, 1)="N", "FALSE", "TRUE")

Caveats

• this won't work if you change the names of the meals
• this won't work if you change the YES/NO parameter