Skip to main content

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:

OriginalBreakfastLunchDinner
Breakfast-NO|Lunch-YES|Dinner-YESFALSETRUETRUE 

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.

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

Reply


Terms & Conditions