Extracting Text|Power Query|Excel Forum|My Online Training Hub

Hi Robert,

There is no pattern matching in Power Query yet unfortunately.

But you can build your custom pattern matching, like I did in the attached file.

What I did:

First, I created a list of items to be replaced, in this case we will replace all digits:

List1 = List.Transform({0..9}, each Text.From(_)),

Next, for each digit in List1, we need to provide a replacement. I created then a list of replacements for each item in List1 using: List.Repeat({1},List.Count(List1)). So each digit will be replaced with 1 (you can replace with any text).

ReplacementsList = List.Zip({List1,List.Repeat({1},List.Count(List1))}),

In the final step, I add a new column, replacing all digits from Legal column with 1

Using Text.PositionOf ..., "11-11-11",Occurence.all) we identify the starting position of all matches for "11-11-11" pattern (any digit was replaced with 1, so our pattern is extremely simple to use now). With the list of positions provided, we simply extract from Legal column 8 chars, for each match: Text.Range(x[Legal],_,8)) . In the final step, combine all results using ";" as separator.

Here are all steps combined into 1:

#"Added Custom" = Table.AddColumn(#"Added Index", "Matches", (x)=> Text.Combine(List.Transform(Text.PositionOf(Text.Combine(List.Transform(List.ReplaceMatchingItems(Text.ToList(x[Legal]),ReplacementsList),Text.From)),"11-11-11",Occurrence.All),each Text.Range(x[Legal],_,8)),";") ),

With the list of matches in this new column, all you have to do is to split this column into rows by ";" delimiter (advanced options: split into rows instead of columns)

Hi Robert,

The name ReplacementList was not recognized where? In the sample file I sent, if you read the query, you will see it, open the advanced editor:

If it's not in your query, you should add it, copy the red lines from above into your query, before adding the new column.