Extract Cetrain Characters from Text Column

[jerryr125]

Hi - I have a text column in a table and I would like to extract a certain number of characters into a new column.   

Example:

 Value:

This is a test of text 255444-01-and a bunch more text

I would like to extract the 255444-01

so therefore start with the two (which is a consistant starting point of the value) and end with -## (and two digits).

The placement of "255444-01" and the amount of characters before and after can very.  The consistancy is that the string I wish to extract begins with a '2' and there is a '-' in the 7th character of the string.

I am not sure this can be done but thought I would ask - thanks - Jerry

[SundarRaj]

Hi @jerryr125 , here's an idea you possibly take a look at. I'll leave the code and output belwo for your reference. Thanks!

[jerryr125]

Hi - can you send me the code or paste the code in the thread so I can copy/modify/paste ? This looks like the solution.

Thanks - Jerry

[SundarRaj]

Hi @jerryr125 , here is the code that you can paste in your advance editor (change the source table):

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),

Custom1 = Table.TransformColumns(#"Changed Type", {"Column1", each List.Select(Text.Split(_, " "), each not (try Number.From(Text.Start(_,1)))[HasError])}),

#"Expanded Column1" = Table.TransformColumns(Custom1, {"Column1", each Table.ExpandListColumn(_, "Column1"), {"Column1", each _}}),

#"Split Column by Position" = Table.SplitColumn(#"Expanded Column1", "Column1", Splitter.SplitTextByRepeatedLengths(9), {"Column1.1", "Column1.2"})[[Column1.1]]

in
#"Split Column by Position"

If you found this solution useful, please consider marking it as the solution.

[v-aatheeque]

Hi @jerryr125 ,

If our response addressed by the community member for  your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[v-aatheeque · Accepted solution]

Hi @jerryr125 ,
here is the code as you requested :

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    Custom1 = Table.TransformColumns(#"Changed Type", {"Column1", each List.Select(Text.Split(_, " "), each not (try Number.From(Text.Start(_,1)))[HasError])}),
    #"Expanded Column1" = Table.TransformColumns(Custom1, {"Column1", each Table.ExpandListColumn(_, "Column1"), {"Column1", each _}}),
    #"Split Column by Position" = Table.SplitColumn(#"Expanded Column1", "Column1", Splitter.SplitTextByRepeatedLengths(9), {"Column1.1", "Column1.2"})[[Column1.1]]
in
    #"Split Column by Position"

Note : Change the table name as per your data. 

Regards,
v-aatheeque 

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!

[AmiraBedh]

You need to find position of all 2 characters (could be more than one as I understood), thenxtract 9 characters starting from that 2 and check if the 7th character is "-" :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY3BCsMgEER/ZfBcIVpzyLXnnkpv4sE2JhGqFteAn99NTh0WluXNzFornlsk8Hi0QA1l4d0b9DgaY+Sg4PPM8LXn94ZUaji5cBcr7nwlxC/tCVrpqxnlNIFK+vPcwnJk9HRIDgP80kI90YObCyc7S/YONibPn80hqRRipjgHfEpeQwW1GvMqnPsB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Original Value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Original Value", type text}}),

   
    #"Added Start Position" = Table.AddColumn(#"Changed Type", "StartPos", each Text.PositionOf([Original Value], "2", Occurrence.First)),

    
    #"Added Candidate" = Table.AddColumn(#"Added Start Position", "Candidate", each try Text.Middle([Original Value], [StartPos], 9) otherwise null),

    #"Filtered Valid Pattern" = Table.AddColumn(#"Added Candidate", "Extracted Value", each 
        let
            txt = [Candidate]
        in
            if txt <> null and Text.Middle(txt, 6, 1) = "-" then txt else null
    ),


    #"Removed Extra Columns" = Table.SelectColumns(#"Filtered Valid Pattern", {"Original Value", "Extracted Value"})

in
    #"Removed Extra Columns"