Selecting text between a list of end delimiters

[Shanks_403]

Hello,

I'm fairly new to PowerQuery and looking for some help. I'm trying to extract a text where the end delimiter varies. The Extract function only lets me select one type of end delimiter. I'm trying to get everything after the WR# and before the "-", " ", or "-".

Original Table                                      Desired Outcome

WR#84658 - For Material                    84658

This WR#8457231 is for chairs            8457231

Received WR#12394. Thanks              12394

Any help would be greatly appreciated!

[ronrsnfld]

Add a custom column with the formula:

Replace Phrase with the name of the column containing your phrases

Text.SplitAny(
Splitter.SplitTextByDelimiter("WR#")([Phrase]){1}," -."){0}

[halfglassdarkly]

Also check out this solution as another option: https://community.powerbi.com/t5/Power-Query/PowerQuery-Text-before-multiple-Delimiters/td-p/1955908

[halfglassdarkly]

As you've found, you can use (under the ribbon menu):

Transform > Extract > Text Between Delimiters.

I think you'll have to apply a couple of subsequent transform steps to extract text before your other two delimiters.

In your example your first delimiter would need to be " " not "-" unless you want a trailing space after the number, so you may be able to limit your end delimiters to " " and "." ?.

If you don't want this to appear as multiple transform steps you could add a custom column referencing [Outcome] and use nested instances of Text.BetweenDelimiter and Text.BeforeDelimiter to achieve the same output.

See also https://learn.microsoft.com/en-us/powerquery-m/text-functions for documentation on the M functions behind this and other text transformation options. 

[Shanks_403]

Thanks for your reply!

The text between delimiters under the Transform>Extract>Text Between Delimiters only lets me pick one End delimiter. In my case, there could be several different types of end delimiters. Is there a way to use that function for this scenario?

[halfglassdarkly]

Yes, sorry re-read your question after posting and realised that. I've amended my original reply.