Long time listener, first time caller here. Typically I can find some great information here on problems I'm trying to solve but I'm stumped with my current one. I have a text field where I'm trying to extract two pieces of information from. This particular field can be up to 109 values long. The first bit of information that I'm trying to retrieve is the particular Cohort grouping and the second piece of information is a network name, such as "Traditional Select" or "Advantage". The issue I have is that the field can have something like "Cohort Cohort B-Preferred Update" and if I use the following expression (Cohort A|Cohort B|Cohort C||Cohort D|Cohort Sample) I get Cohort C as the output because of the duplicated "Cohort Cohort" in the field. Also, how do I then get the second part of the data I need having a combination of spaces and dashes after the cohort name?
Below is a sampling of the data, the first column is a field that I have, and the two right columns is what I'm trying to achieve. Of note, the Description field can contains varying names of different lengths, a combination of text and numbers, it doesn't always begin with the string "Cohort".
| Description | Cohort | Network |
| Cohort A-Advantage (Open 30) | Cohort A | Advantage |
| Cohort A-HMO | Cohort A | HMO |
| Cohort B - Performance | Cohort B | Performance |
| Cohort B - Preferred Network | Cohort B | Preferred |
| Cohort B-Advantage (Open 30) | Cohort B | Advantage |
| Cohort B-Performance | Cohort B | Performance |
| Cohort C-Advantage (Open 30) | Cohort C | Advantage |
| Cohort C-HMO | Cohort C | HMO |
| Cohort Cohort A | Cohort A | |
| Cohort Cohort A-Advantage (Open 30) | Cohort A | Advantage |
| Cohort Cohort B | Cohort B | |
| Cohort Cohort B-Advantage (Open 30) | Cohort B | Advantage |
| Cohort Cohort C | Cohort C | |
| Cohort Cohort C-Advantage (Open 30) | Cohort C | Advantage |
| Cohort Cohort D | Cohort D | |
| Cohort Cohort Sample-Performance | Cohort Sample | Performance |
| Cohort C-OK-HMO | Cohort C | HMO |
| Cohort C-Open 90 | Cohort C | Open 90 |
| Cohort D-Performance | Cohort D | Performance |
| Cohort D-Select | Cohort D | Select |
| Cohort Sample-Advantage (Open 30) | Cohort Sample | Advantage |
Thanks in advance!!
