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})

     

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

Hi,

 

Please try the below...

 

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Table", type text}}),
NumbersOnly = Table.TransformColumns(#"Changed Type", {{"Table", each Text.Select( _ , {"0".."9", " "})}}),
#"Trimmed Text" = Table.TransformColumns(NumbersOnly,{{"Table", Text.Trim, type text}}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Trimmed Text", {{"Table", each Text.BeforeDelimiter(_, " "), type text}})
in
#"Extracted Text Before Delimiter"

 

Basically extracting all numbers and spaces from your column (called mine Table), then only keeping numbers before the first space which I think will give you what you need.

 

Thanks,