recognise format of code in Power Query to extract

[ooptennoort]

>10k of rows of short sentences have within then, usually, a code. I need to extract this code, but how do I get PQ to recognise its format, which is:

230F-115-04

301F-115-05

30A-115-26

30F-1180-02

30F-118-03

30F-1252-04

130H-1252-05

30F-1252-18

30F-126-01

40G-127-01

30F-108-01

31F-299-91

30F-130-01

30F-131-06

30F-132-01

I've tried with text.before and between and after, but have never gotten just -and all of- the code. Very challenging. Help is very much appreciated (if want I want is possible at all ).

 

[GraH]

Hi,
So you want to lookup any of these codes in a column of text?
There is no direct function for this, at least not that I know off. However you can write it. Try this tutorial.

https://www.myonlinetraininghub.com/searching-for-text-strings-in-power-query

 

[ruthhacche]

John McDougal wrote a post I use to bulk replace data in tables and I am guessing the code could be adjusted to extract codes (rather than replace them) if you have them in a table that can be referred to.

Bulk Find And Replace In Power Query | How To Excel

www.howtoexcel.org

 

[ooptennoort]

Hi,
So you want to lookup any of these codes in a column of text?
There is no direct function for this, at least not that I know off. However you can write it. Try this tutorial.

https://www.myonlinetraininghub.com/searching-for-text-strings-in-power-query

Problem is, I don't have the codes. There may be millions. So I can't lookup. All I can do is recognise the code format/structure. Maybe if I can use your suggestion by looking for excel wildcards, somehow... but the variation ???-???-?? or ??-??-?? or ???-??-?? or ??-???-?? make it difficult...

 

[ooptennoort]

John McDougal wrote a post I use to bulk replace data in tables and I am guessing the code could be adjusted to extract codes (rather than replace them) if you have them in a table that can be referred to.

Bulk Find And Replace In Power Query | How To Excel

www.howtoexcel.org

Thank you both, btw! Like I said to Grah, problem is, I don't have the codes. There may be millions. So I can't lookup. All I can do is recognise the code format/structure. Maybe if I can use your suggestion by looking for excel wildcards, somehow... but the variation ???-???-?? or ??-??-?? or ???-??-?? or ??-???-?? make it difficult...

 

[GraH]

Problem is, I don't have the codes. There may be millions. So I can't lookup. All I can do is recognise the code format/structure. Maybe if I can use your suggestion by looking for excel wildcards, somehow... but the variation ???-???-?? or ??-??-?? or ???-??-?? or ??-???-?? make it difficult...

I see. Could you upload a small, but representative sample of data via xl2BB?
PQ does offer example from column (much like flash fill), but I doubt it will work.

 

[ooptennoort]

I see. Could you upload a small, but representative sample of data via xl2BB?
PQ does offer example from column (much like flash fill), but I doubt it will work.

Using Column from examples is the first thing I tried and it worked surprisingly well, but I think because the sample size (999 records?) was too small... and >999 of the first cells contained my code, that batch was 100% perfect, but where code was missing Column from examples just provided the whole cell content (problem description) instead of empty cell. Column from examples also missed the (rare) other formats (???-???-?? and ???-????-??).
Using xl2BB is something I will try asap (but I am not sure if it is necessary... will have to try it, not familiar with it)! Thank you!

 

[GraH]

I ask for the sample, just to have a view on the patterns. I doubt it is ok to look for "-" and build around it. Last space before and first space after and stuff like that. Samples make it easier to assess if we are on a good track or not.

 

[GraH]

Hi @ooptennoort ,
Would this do the trick for you?

Book1
	A	B
1	Comment	GetCodes
2	bla blabla bla 230F-115-04 blablablabla blablabla blablablabla blablabla blabla	230F-115-04
3	blabla blablablablablabla blablabla bla 301F-115-05 blabla bla	301F-115-05
4	bla bla bla bla bla bla bla blablablablablablablablablabla 30A-115-26 blablablablablablablabla blablablablablabla blabla	30A-115-26
5	blablablablablabla 30F-1180-02 blablablablablablablablablablablablabla	30F-1180-02
6	blablablablablablablablablabla 30F-118-03 blablablabla 130H-1252-05	30F-118-03, 130H-1252-05
7	blablablablablablablablablablablabla 30F-1252-04 blablablablablablablablablabla	30F-1252-04
8	blablablabla400 blablablablabla 130H-1252-05 blablabla	130H-1252-05
9	5000 blablablablablablablablablablablablablablablablablablabla 30F-1252-18 blablablablablablablablablablablablablablablablabla	30F-1252-18
10	blabla 30F-126-01 blablablablablablablablabla	30F-126-01
11	blablablablablablablablablablablablabla 40G-127-01 blabla	40G-127-01
12	blablablablablablablablablablablablabla 30F-108-01 blablablabla	30F-108-01
13	blablabla 31F-299-91 blablablablablablablablablablablablablablablablabla	31F-299-91
14	blablablablablablablablablablablablablablablablablablabla 30F-130-01 blablablablablablablablablablablablablablabla	30F-130-01
15	blablablablablablablablablablablablablablablabla 30F-131-06 blablablablablablablablabla	30F-131-06
16	301F-115-05 blabla 30F-132-01 blablablablablablablablablablablabla 105	301F-115-05, 30F-132-01
17	30F-130-01 blablabla	30F-130-01
18	blablabla 30F-130-01	30F-130-01
Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Comment", type text}}),
    GetCodes = Table.AddColumn(#"Changed Type", "GetCodes", each let Words = Text.Split([Comment]," "),
Codes = List.Select(Words, each List.AllTrue({ Text.Contains(_, "-"), 
Text.PositionOfAny(_, {"0".."9"} ) <> -1,
Text.PositionOfAny(_, {"A".."Z"} ) <> -1})),
Combine = Text.Combine(Codes, ", ") 
in 
Combine)
in
    GetCodes

 

[Bo_Ry]

Use data from GraH

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Regex = Table.AddColumn(Source, "Regex", each  Text.Combine(Json.Document(Web.Page(
    "<meta http-equiv='X-UA-Compatible' content='IE=edge'> <script>
    document.write(JSON.stringify('"& [Comment] &"'.match(/\d+[A-Z]-\d+-\d+/g)));
    </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}),", "))
in
    Regex

Power Query Regex.xlsx
	A	B
1	Comment	Regex
2	bla blabla bla 230F-115-04 blablablabla blablabla blablablabla blablabla blabla	230F-115-04
3	blabla blablablablablabla blablabla bla 301F-115-05 blabla bla	301F-115-05
4	bla bla bla bla bla bla bla blablablablablablablablablabla 30A-115-26 blablablablablablablabla blablablablablabla blabla	30A-115-26
5	blablablablablabla 30F-1180-02 blablablablablablablablablablablablabla	30F-1180-02
6	blablablablablablablablablabla 30F-118-03 blablablabla 130H-1252-05	30F-118-03, 130H-1252-05
7	blablablablablablablablablablablabla 30F-1252-04 blablablablablablablablablabla	30F-1252-04
8	blablablabla400 blablablablabla 130H-1252-05 blablabla	130H-1252-05
9	5000 blablablablablablablablablablablablablablablablablablabla 30F-1252-18 blablablablablablablablablablablablablablablablabla	30F-1252-18
10	blabla 30F-126-01 blablablablablablablablabla	30F-126-01
11	blablablablablablablablablablablablabla 40G-127-01 blabla	40G-127-01
12	blablablablablablablablablablablablabla 30F-108-01 blablablabla	30F-108-01
13	blablabla 31F-299-91 blablablablablablablablablablablablablablablablabla	31F-299-91
14	blablablablablablablablablablablablablablablablablablabla 30F-130-01 blablablablablablablablablablablablablablabla	30F-130-01
15	blablablablablablablablablablablablablablablabla 30F-131-06 blablablablablablablablabla	30F-131-06
16	301F-115-05 blabla 30F-132-01 blablablablablablablablablablablabla 105	301F-115-05, 30F-132-01
17	30F-130-01 blablabla	30F-130-01
18	blablabla 30F-130-01	30F-130-01
Sheet1