Skip to main content
Solved

Extract Values from String in three different places

  • February 16, 2023
  • 1 reply
  • 25 views
J
Forum|alt.badge.img+2

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.

Best answer by pressGO_design

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

1 reply

pressGO_design
Forum|alt.badge.img+21
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
Terms & ConditionsAccessibility statement