How do I extract a 7 character phrase after a certain 3 character string?

[chall069]

How do I extract a 7 character phrase after a certain 3 character string? I need to extract the 7 character string after the phrase "PRB". I have tried using the various text after delimiters functions in power query but it does not work because there is not consistant format.

Here is some sample data. For each of these rows I just need the 7 characters after "PRB".

M- PRB0056046-A (<2/22): Issues w/multiple service, S & E

Not a -PRB0056058-RCA: INC12133 -   Prod

Postmortem IR-12: PRB0056579-RCA File upad/dowoad were failing in

P-PRB0055925-RCA:  Users add in were g added doweam

[parry2k]

@chall069 add a new column using the following M expression, make sure the exact column name is used from your table. Power Query is case-sensitive.

Text.Combine(List.Transform(List.RemoveFirstN(Text.Split([TEXT],"XX"),1),each Text.Combine({"XX",Text.Start(_,8)})),",")

[pawel_lyszczarz]

This seems to work well with my case.
Could you elaborate on how the code works? I understand that it adds the column that combines each text with OH- and # of characters after. What does "List.RemoveFirstN" do? 

Could this be modified to work with multiple delimiters? Or would I need to somehow duplicate that and add other delimiters and then combine?

[parry2k]

@pawel_lyszczarz is this correct assumption to have value after "xx"

[parry2k · Accepted solution]

@chall069 add a new custom column in PQ and add the following expression:

Text.Start(Text.AfterDelimiter([Your Column Name],"PRB"),7)

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[pawel_lyszczarz]

Any idea how to extract the text with the delimiter? And maybe if there are more than one?
I would need a code to also look for next match after the first one. 
Another approach would be split column by delimiter, then add a prefix, but I'm left with rows that contain "the rest" of text.
Match is always 10 characters in my data.
Example:

Text	Extraction
Here's a XX-12-1234, and here's a XX-56-7890	XX-12-1234; XX-56-7890

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Combine(List.Transform(Text.Split([Text],","), each "XX"&Text.AfterDelimiter(_,"XX")),", "))
in
    #"Added Custom"

Hope this helps.

[pawel_lyszczarz]

What if text doesn't have "," after the first and next matches?
This was only an example, text in my data can look like this too:

"FOR EXAMPLE XX-11-1234 AND OTHER EXAMPLES XX-56-7890. JUST A FILLER TEXT, NO REPEATING DELIMITERS XX-55-6666 AND SO ON"

[Ashish_Mathur]

You might as well take a representative sample with all complications that you have and show the expected result for that sample.

[pawel_lyszczarz]

Any help or article on how to do that?
I tried using Custom Column from Examples but it gives a code to extract text between delimiters which as I've said aren't typical. When given 3 of the "Extraction" samples, it gives "null" in the rest. Is it possible to split column before not after the delimiter? I could split that into rows and then just extract first 10 characters from each.

[Ashish_Mathur]

As requested in my previous message, please share some representative samples with all complications that you have and show the expected result for that sample

[pawel_lyszczarz]

Please see this example, "XX-" is always in the extracted text.
"XZ", "YZ", "XY" changes across the data. Text to be extracted is always 10 characters.

There's sometimes delimiters, but that's not typical.

TEXT	EXTRACTION
FOR ADDITIONAL INFORMATION SEE XX-YY-1234 OR XX-YY-5678	XX-YY-1234, XX-YY-5678
IF NOT SHOWN REFER TO XX-YZ-1234, OPTIONALLY XX-ZZ-1234, XX-XZ-1234.	XX-YZ-1234, XX-ZZ-1234
FOR CONDITION SHOWN, SEE XX-XZ-1234. FOR INFORMATION NOT SHOWN ON XX-XZ-1234 SEE XX-YZ-5678 OR XX-ZY-1234	XX-XZ-1234, XX-XZ-1234, XX-YZ-5678, XX-ZY-1234

[chall069]

This is perfect, thank you!