Extract target text from one master column

[llan]

Running a campaign and asking a sales team to add their input in a note section in our opportunity tracking system by adding the following template and answering the questions asked.

#CampaignNAME

Proposed the topic to Customers (Y/N):
If Yes, Next Steps:
If NO, Reasons: XXXX END

When we pull the report, it will look like the yellow column for each pursuit. The target is to extract information between “#CampaignNAME” and “END” and then split target information to separate columns like the blue ones below. I also provided sample answers. The sales team may fill in the information as required or not. So the column will not have #CampaignNAME, meaning the sales team has not updated them yet.

How do I build calculated fields to get those blue columns?

[Xclipse]

Hi @llan

Using locate, substring and trim function, you can extract the specific part from string.

Following example is for the “Proposed the topic to Customer” from your sample data.

Example:

trim(ifelse(
    locate(Description,': Yes', 1) > 0, substring(Description,locate(Description,': Yes', 1) + 2, 4),
    ifelse(
        locate(Description,': NO', 1) > 0, substring(Description,locate(Description,': NO', 1) + 2, 3),
        ifelse(
            locate(Description,': N', 1) > 0, substring(Description,locate(Description,': N', 1) + 2, 2)
        , 'N/A')
    )
))

[llan]

Thanks. I did a test by adding a sample entry to the cell under the column of Description. Please see below, However, the above suggested formulation come back with N/A results. I am expecting the outcome to be “NO”. Did I miss anything?

#NOVA-Injection 1. Proposed NOVA to customers (Yes/No): NO 2. If Yes, next steps: 3. If No, reasons: Customer is not interested END

[Xclipse]

Hi @llan

I tried the string you shared and used the same calculation I previously shared, but I’m getting ‘NO’. Please see the following images for your reference.

[Xclipse]

Hi @llan

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!