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!



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!