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!



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!



  • SundarRaj's avatar
    SundarRaj
    Super User
    1 year ago

    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's avatar
      v-aatheeque
      Community Support
      1 year ago

      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!