Text parser to Google Sheet - struggling with formatting

[firetech]

Hello: I’m trying to parse the fields below (Company, Name, Email address, Job title, Phone numbers, and Message), but I am unable to get the Pattern syntax to work. Any help is appreciated - output bundle below

[
    {
        "result": "Company: test department###Name: John Smith###Email address: test@test.com###Job Title: test rank###Phone numbers: test###Message: test message",
        "id": "chatcmpl-AbVIDA4iPGREHeX8zdPWVkRbfMCpc",
        "object": "chat.completion",
        "created": "2024-12-06T16:16:29.000Z",
        "model": "gpt-4o-mini-2024-07-18",
        "choices": [
            {
                "index": 0,
                "message": {
                    "role": "assistant",
                    "content": "Company: test department###Name: Marcus Edwards###Email address: test@test.com###Job Title: test rank###Phone numbers: test###Message: test message",
                    "refusal": null
                },
                "logprobs": null,
                "finish_reason": "stop"
            }
        ],
        "usage": {
            "prompt_tokens": 425,
            "completion_tokens": 32,
            "total_tokens": 457,
            "prompt_tokens_details": {
                "cached_tokens": 0,
                "audio_tokens": 0
            },
            "completion_tokens_details": {
                "reasoning_tokens": 0,
                "audio_tokens": 0,
                "accepted_prediction_tokens": 0,
                "rejected_prediction_tokens": 0
            }
        },
        "system_fingerprint": "fp_0705bf87c0"
    }
]

[Foivos]

Hi @firetech.

From your output bundle it seems like the “company”,“name” etc fields that you want are not actually fields at all. They are all contained in the value of the first element “result”.
In order to get these values each time there is some data manipulation that you must do first.

Step 1 is to get the value of result.
Step 2 is to split this into several texts to separate the desired values, something like split(data;###). That way you end up with different texts that each of them somewhat contains the info you want.
Step 3 is to Iterate through your outputs from step 2. You still have a text but no Key:Value pairs to use “name” etc.
Step 4 is to aggregate it all together into a Json format
Step 5 is to use a Json parser to create your final form of keys and values so you can use whatever is returned.

Here is what you should do. I made an example with the “result” text, so I am bypassing step 1:

That’s the way to achieve this with the minimum operations.

[Foivos]

that should give you this as a final result

[
{
“Company”: “test department”,
“Name”: “John Smith”,
“Email address”: “test@test.com”,
“Job Title”: “test rank”,
“Phone numbers”: “test”,
“Message”: “test message”
}
]