How do I extract a specific text with a specific text length?

Hi Anonymous, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcxBC4JAFATgvzLsOYQ219RjBnUM6iYeFnvqwurGe1vgv2+xa7dhmPnaVjUcRHB1TBgCowlLZNtH6Kowpep2rbq9uZ+skKDGmbz7EK9IDY8k2+BHTH+IXFfbQsNFmqXGKXhPeDkfIu52oLjiEsbRJ7zxZDnLsu3wIInY60NukMTC5McSdnmmUBjVdV8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cross hire for Contract" = _t]),
    Ad_Extract = Table.AddColumn(Source, "Extract", each 
        [ a = {"0".."9"},
          b = Splitter.SplitTextByCharacterTransition((x)=> not List.Contains(a, x), a)([Cross hire for Contract]),
          c = List.Transform(b, (y)=> Splitter.SplitTextByCharacterTransition(a, (x)=> not List.Contains(a, x))(y)),
          d = List.Select(List.Combine(c), (x)=> (try Number.From(x) otherwise false) is number and Text.Length(Text.Trim(x)) = 5),
          e = Text.Combine(d, ", ")
        ][e], type text)
in
    Ad_Extract

I can't tell from that sample size, but if all of your values are at the end of the string, you can just use Text.End([ColumnName], 5).

--Nate

Add a custom column:

= let 
numbers = Text.Select([Test], {"0".."9"})
in
if Text.Length(numbers)=5 then numbers else null

Even easier, just append a space to your text column, And then no matter where you're five digits are, you can use Text.BetweenDelimiters with "Cross Hire for Contract" and " " as your delimiters.

--Nate

Hi all,

The insights of the super users are very good. Here I would like to share a blog that is about executing regular expressions with JavaScript in Power Query Editor.

In this blog, you will be shown how to use regular expressions to get strings in a specific format from a column and I think for this post is a solution.

Blog: Explore Web.Page in power query: Execute regular e... - Microsoft Fabric Community

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

let
    Source = your_table,
    nums = "0123456789",
    func = (txt) => 
        [cln = List.Transform(Text.ToList(txt), (x) => if Text.Contains(nums, x) then x else " "),
        t = Text.Combine(cln),
        split = Splitter.SplitTextByWhitespace()(t), 
        combine = Text.Combine(List.Select(split, (x) => Text.Length(x) = 5), ", ")][combine],
    add_col = Table.AddColumn(Source, "5 digits", each func([Cross hire for Contract]))
in
    add_col