Help

Re: Extract Values from String in three different places

Solved
Jump to Solution
541 0
cancel
Showing results for 
Search instead for 
Did you mean: 
junaid2ali
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

1 Solution

Accepted Solutions
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

See Solution in Thread

1 Reply 1
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