Forum Discussion

Trebor84's avatar
Trebor84
Helper II
3 years ago
Solved

Extract first occurring number set from text string

Hi, I'm trying to extract the first group of numbers from a text string in Power Query editor . Have seen a few tips on the this but problem is that the strings don't have any set pattern.   Please...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    3 years ago

    You can use below in a custom column

     

     

    try Number.From(Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c))(Text.RemoveRange([Text],0,Text.PositionOfAny([Text],{"0".."9"}))){0}) otherwise null

     

    Edit - another construct can be

    Number.From(Text.Split(Text.Trim(Text.Combine(List.Transform(Text.ToList([Text]), (x)=> if not List.Contains({"0".."9"},x) then " " else x)))," "){0})

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
3 years ago

You can use below in a custom column

 

 

try Number.From(Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c))(Text.RemoveRange([Text],0,Text.PositionOfAny([Text],{"0".."9"}))){0}) otherwise null

 

Edit - another construct can be

Number.From(Text.Split(Text.Trim(Text.Combine(List.Transform(Text.ToList([Text]), (x)=> if not List.Contains({"0".."9"},x) then " " else x)))," "){0})

 

  • Trebor84's avatar
    Trebor84
    Helper II
    3 years ago

    Thanks, all of these work great. I went with the top solution.