Comment Post Options
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 16, 2023 02:46 PM
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.
Solved! Go to Solution.
1 Solution
Accepted Solutions
Solved
See Solution in Thread
Comment Post Options
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 17, 2023 06:03 AM
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 Reply 1
Solved
See Solution in Thread
Comment Post Options
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 17, 2023 06:03 AM
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