Extract data in middle of sentence after and before other words. All sentenes are different lengths

[KarenFingerhut]

Hi there

Hope someone can help me please

I need to extract a certain word in a sentence that contains a multitude of info. Below is an example sentence

Today the weather is lovely and WorkSpaceName, "sunny", DatasetName, "but tomorrow", it is going to be rubbish, typical UK

The word I want to extract is Sunny. I'm really struggling to find some dax to enable me to extract after WorkSpaceName and before DatasetName.

There are lots of other special characters in the string sentence so I can't just use a special character, and the words I want to extract aren't always the same in length

Any help would be really appreciated

Many thanks

Karen

[Alican_C · Accepted solution]

What about creating a separate column?

I would go to Power query and pick the Text Between Delimiters option where you can set your Start and End delimiter and the desired outcome will be on a separate column.

[KarenFingerhut]

😀Perfect @Alican_C worked a treat. Thanks for your help. Kind regards Karen