Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Extract 5 Consecutive Numbers from Text String

Hello Everyone,

 

This is my first post so please let me know if I need to change anything.  I have a need to extract a 5 digit number from a text string.  Unfortunately there wasn't a specific format set for this string so the 5 digits I need appear in different locations of the string.  I have provided an example below of what I need.  What is consistent is that there is only ever 5 consecutive numbers once per string and they always follow a period.  

 

Starting StringExtract
C402.asdfl.43582.243582
F423D.6.62418.LJ486462418
LDOFK.48673.5.KGFO48673
G453.14162.9.LDKFI14162
C532.15354GHC.215354

 

I have tried a variety of methods but so far no luck.  If you have any ideas, please let me know.  

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi Anonymous ,

     

    Paste this code over the default code in a new blank query to see the steps I took to complete this:

    let
    removeChars = List.Transform({65..90, 97..122}, each Character.FromNumber(_)),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HY1LCgIxEETv0uuhMP2JcZ2QqAnMAYYsBHHlzvuDmd71q1dFHwdlvTBev/fnCxVLDKaN/KK5HVSVpSAisoaE8dQUdRUcvTDKXjtWfBUYeqv7uT/RdVMTBA2RccMovT6WdnadTRjBxLTds792oDn/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Starting String" = _t, Extract = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Starting String", type text}, {"Extract", Int64.Type}}),
    addCalcOutput = Table.AddColumn(chgTypes, "calcOutput", each Text.Remove([Starting String], removeChars)),
    splitByDelimiter = Table.ExpandListColumn(Table.TransformColumns(addCalcOutput, {{"calcOutput", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "calcOutput"),
    filterLengthFive = Table.SelectRows(splitByDelimiter, each Text.Length([calcOutput]) = 5)
in
    filterLengthFive

     

    Summary:

    1) Create list of all letters, lower and upper case.

    2) Use list to remove all text characters from starting string.

    3) Split resulting values by "." delimiter into new rows.

    4) Filter column on value length = 5.

     

    This gives me the following output ([calcOutput] column):

     

    Pete

4 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi Anonymous ,

     

    Paste this code over the default code in a new blank query to see the steps I took to complete this:

    let
    removeChars = List.Transform({65..90, 97..122}, each Character.FromNumber(_)),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HY1LCgIxEETv0uuhMP2JcZ2QqAnMAYYsBHHlzvuDmd71q1dFHwdlvTBev/fnCxVLDKaN/KK5HVSVpSAisoaE8dQUdRUcvTDKXjtWfBUYeqv7uT/RdVMTBA2RccMovT6WdnadTRjBxLTds792oDn/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Starting String" = _t, Extract = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Starting String", type text}, {"Extract", Int64.Type}}),
    addCalcOutput = Table.AddColumn(chgTypes, "calcOutput", each Text.Remove([Starting String], removeChars)),
    splitByDelimiter = Table.ExpandListColumn(Table.TransformColumns(addCalcOutput, {{"calcOutput", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "calcOutput"),
    filterLengthFive = Table.SelectRows(splitByDelimiter, each Text.Length([calcOutput]) = 5)
in
    filterLengthFive

     

    Summary:

    1) Create list of all letters, lower and upper case.

    2) Use list to remove all text characters from starting string.

    3) Split resulting values by "." delimiter into new rows.

    4) Filter column on value length = 5.

     

    This gives me the following output ([calcOutput] column):

     

    Pete

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      4 years ago

      Pedantic suggestion:

      Whitelisting digits rather than blacklisting letters is slightly simpler and slightly more robust.

       

      That is, instead of Text.Remove([Starting String], removeChars), use Text.Select([Starting String], {".", "0..9"}).

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      This worked perfectly!  Thank you!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks BA_Pete ! I used this to extract times written as a string of four consecutive digits from a column of long-form text strings.


      The changes I had to make:

      • changing source to an existing table from another query
      • space as delimiter instead of "."
      • need to exclude other non-numeric characters from the string

      My adapted code below in case it's useful for future readers.

       

      = let
    removeChars = List.Transform({33..47, 58..90, 97..122}, each Character.FromNumber(_)),
    Source = My_Source,
    chgTypes = Table.TransformColumnTypes(Source,{{"My_Text_Field", type text}, {"My_Output_Column", Int64.Type}}),
    addCalcOutput = Table.AddColumn(chgTypes, "calcOutput", each Text.Remove([My_Text_Field], removeChars)),
    splitByDelimiter = Table.ExpandListColumn(Table.TransformColumns(addCalcOutput, {{"calcOutput", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "calcOutput"),
    filterLengthFour = Table.SelectRows(splitByDelimiter, each Text.Length([calcOutput]) = 4)
in
    filterLengthFour