Forum Discussion

GillesDesbois's avatar
GillesDesbois
Regular Visitor
6 years ago
Solved

Select a variable part in another cell

hello
my question is :

I have a column with data like :

02009bat2555 - 3605054429035.pdf

3605052926581 nouveau logo.pdf

I need to extract only the 13 digits "360..." in a new column.
You can see that the 13 digits are never in the same place (I have an infinity of combination)

Do you think it's possible to have a function (or combination of functions) tio do this ?

thanks for your help

Gilles

Need Help

2 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi GillesDesbois ,

    Yes it is possible. I used PowerQuery. I used Text.PositionOf, and Text.Middle.

    See pic and Adv Editor. You could combine this into one column, but left it this way for ease of understanding.

     

    Please let me know if you have any questions.

     

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

     

    e

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYzMDTVK0hJU9JRUlCK1YGImFlYAvlgromJqakZUMzUFCwSCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.PositionOf([Column1], "360")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Middle([Column1],[Custom],5))
in
    #"Added Custom1"

     

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    I used 5 for my count number, rather than 13.