Forum Discussion

jerryr125's avatar
jerryr125
Helper IV
1 year ago
Solved

Extract Cetrain Characters from Text Column

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

 

Need Help
Show and Tell
  • v-aatheeque's avatar
    v-aatheeque
    1 year ago

    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!



6 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    1 year ago

    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"

     

     

  • SundarRaj's avatar
    SundarRaj
    Super User
    1 year ago

    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's avatar
      jerryr125
      Helper IV
      1 year ago

      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

      • v-aatheeque's avatar
        v-aatheeque
        Community Support
        1 year ago

        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!