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 ...
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!



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"