How to extract ID numbers from a text string?

[Anonymous]

Hi,
@ImkeF
@Zubair_Muhammad

Now I’m trying to solve a slightly more complex situation. I’m trying to extract the 5 and 6 length ID numbers for UK and France.



This is my current approach.

Each country has a set of rules;

UK
1) Cut off first 3 characters from the string
2) Cut off all characters before and including /
3) Cut off all characters after and including ?

FR
1) Cut off first 4 characters from the string
2) Cut off all characters before and including @
3) Cut off all character after and including ?

So then I follow this procedure;

ID =

// Var UK
Var UK1 = Cut off first 3 characters from the string
Var UK2 = Cut off all characters before and including /
Var UK3 = Cut off all characters after and including ?
Var UK = Var UK 3

// Var FR
Var FR1 = IF(Page = Var UK THEN Var UK ELSE Cut off first 3 characters from the string)
Var FR2 = Cut off all characters before and including @
Var FR3 = Cut off all character after and including ?
Var FR = Var FR 3

// Var ID
Var ID = Var FR

Return
Var ID

Am I working in the most efficient way? Are there better solutions?

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try this. I am not 100% sure.

Please could you paste data in copiable format instead if image

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvXWL0tMTsxLrtS3NDQyMrPX003NLcjJr0xNVYrViVZyC3LUBwtkFqcUJ6YkGpkYx5QaGBin6jtYGIKAia6evlJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Page = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Page", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Start([Page],2)="UK" then
                                                                    Text.BetweenDelimiters([Page],"/", "?",1)
                                                                    else
                                                                    if Text.Start([Page],3)="FRA"
                                                                    then
                                                                    Text.BetweenDelimiters([Page],"@", "-")
                                                                    else
                                                                    null)
    in
    #"Added Custom"

[Anonymous]

@Zubair_Muhammad

Thanks very much. I will try tommorow morning.
Yes, I will paste the data in the right format tommorow morning.

Thanks again

[Anonymous]

@Zubair_Muhammad

It works ! 🙂

Thanks very much. I'm learning a lot about text extraction.



Now, I hope I can solve the full problem I have.

Hopefully you can help me with the last steps 🙂

Data

UK/vacancy/91226?.-employee<af44g>3.3u22u37666fff_	91226
UK/vacancy-employee</21226?.->3.3.u376641 fff	21226
UK/vacancy-employee155-</44226?>3.3uu37ff54215f_	44226
FRA/emplois vacants.employé.243>/@811114-./3uu37ff263337f_?	811114
FRA/emplois vacants /employé?<af4fg>aa/@833114-./uu37ff443211f_?	833114
FRA/emplois vacants/employé?a3f44>3. /@133114-./ff4121_?155654	133114
UK/vacancy.11-employee<af4fg>3.3u76117u37fff_/79856?	79856
UK/vacancy-employee<44>3F/91156?.3.u344447f87ff_	91156
FRA/emplois vacants /employé?<af4fg>aa/@133114-./uu37f6442251ff_?	133114
FRA/emplois vacants /employé?a568>3./@187114-./711112 ff_?	187114