Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Extract from Text Column only if there are 10 consecutive digits. no matter where

Hey guys,

I am trying to extract an ID from a text column. The data are a bit messy so there is no pattern, except from the fact that this ID is 10 digits long.

 

I would like a way to extract this 10-digit number in a most robust way possible.

 

Some examples:
         1. Something\Something - more something - 1289098857
          2. Something\Something - more something -1289098857
          2. Something\Something - more12 something ID:1289098857

Thanks in advance for your help!! 🙂

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

     

     

     

    let
    extrnum=(text)=>
    let
    nc = Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"})(text)
in
    List.Last(nc)

    in 
extrnum

    or, more generally

    let
    extrnum=(text)=>
    let
    nc = Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"})(text),
    nn=List.RemoveNulls(List.Transform(nc, each try Number.From(Text.Start(_,10)) otherwise null))
in
    nn{0}

    in 
extrnum

     

3 Replies

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    Here is one way to do it.

     

    = List.Select(List.Transform(Text.Split([TextColumn], " "), each Text.Select(_, {"0".."9"})), each Text.Length(_) = 10){0}

     

     

    Pat

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    could you have other numbers in the text?
    if so, can you give some examples?
    the location of the ID is not necessarily at the end, is it?

     

     

    Table.AddColumn(#"Modificato tipo", "number", each List.Select(Text.SplitAny([text], Text.Combine({" ".."/"}&{":".."}"})), (n)=> Text.Length(n)=10){0}?)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

       

       

       

      let
    extrnum=(text)=>
    let
    nc = Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"})(text)
in
    List.Last(nc)

    in 
extrnum

      or, more generally

      let
    extrnum=(text)=>
    let
    nc = Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"})(text),
    nn=List.RemoveNulls(List.Transform(nc, each try Number.From(Text.Start(_,10)) otherwise null))
in
    nn{0}

    in 
extrnum