Extracting free form text from a long text field

[mku90]

In jira we have an Acceptance Criteria field with LOTS of text and we sync this to airtable because airtable offers more fields necessary for the work we do.

I want to be able to create a formula that finds and returns the free form text after a specific string of text in the long text field.

For example from the long text example further below I want the formula to SEARCH the {Acceptance Criteria} field for  “Creative_Type: " and return the text after it (maybe use “RIGHT”?) but before a hard return and the result in this case would be SIG

I’ve created rules where the options are a static list (for example “Email Type: “can only have a certain selection so my formulas account for that but when the text is free from I just want that field to populate with the free form text that shows after “Creative_Type: “  I assume it would have to be a RIGHT and calculating the length or isolating anything after the "Creative_Type: and before the hard return or do i have to create a combo formula that points  to the RIGHT of Creative Type and to the LEFT of whatever the next line includes (in the example below it would be "Extras:"

Any help is appreciated.  

Example of copy in Acceptance Criteria field

Acceptance Criteria

SFID: 73Z
AcctManager: Olivia 
Business Line: Deposits
SFMC Business Unit: Triggers

Send Classification: Commercial
Email Type: RESEND
Reference ID or Campaign:
Template: Retail
Creative_Type: SIG
Extras: Moveable Ink App
Extra Details: Countdown Timer
Versions:  1
Variables: 5
List Provider:  Campaign Team
Seed List:  Master Seed List
Custom Seeds?:  No
Transactional/Commercial: Commercial
Additional Exclusions: No
Preference Center Exclusions: Code10
Sample Request: 3
Dedupe File: No
Throttle: No

[Ben_Young1 · Accepted solution]

Hey @mku90! 

If I had to solve for this use case, I would lean on regex since I don't like the dance of juggling around a mix of FIND(), RIGHT(), LEFT(), etc. functions.

I pasted your example into a long text field and then wrote a quick formula that follows the following logic:

If the Acceptance Criteria field contains data, check to see if the field's text matches "Creative_Type:"
If that search returns successful, then we extract that line and remove the "Creative_Type:" portion of the text. Finally, we remove any leading or trailing whitespace.

If the Acceptance Criteria field does not contain any data, then it will simply return blank.

Here's the formula that I used:

IF(
    {Acceptance Criteria},
    IF(
        REGEX_MATCH(
            {Acceptance Criteria},
            "Creative_Type:.+"
        ),
        TRIM(
            REGEX_REPLACE(
                REGEX_EXTRACT(
                    {Acceptance Criteria},
                    "Creative_Type:.+"
                ),
                "Creative_Type:", ""
            )
        )
    )
)

There are certainly many other ways you could do it, but this would be my first strategy.

[mku90]

Ben!! you are a lifesaver! I pasted your formula in and bam.. got exactly the result i was looking for.  Thank you so much!  🙏🎉🙂