Isolating 5 digit+ Numbers in Edit Query/Advanced editor

[StephenF]

I am trying to strip out numbers from a string where the numbers are in a chain of 5 or greater numbers.

ie 12345 would be return 12345 but 12 234 wouldnt return anything.

I've listed an example of the input and the desired output below

Any ideas?

Input	Results
1223.com IO 7156765	7156765
IO 159356 Gas Networks europe	159356
Loreal Organics 2020 Nar IO: 159718	159718
Pharmaton IO: 159722 Audio	159722
BGE Shine a Light IO: 159725	159725
Volkswagen  IO: 140938 Video	140938
IBTS 2019 IO: 88675	88675

[CNENFRNL]

Hi, @StephenF , mahoneypat's solution is neat and effetive to cope with your issue.

If your dataset by chance contains such an entry "1223.com IO 7156765and55555", you may want to try a more generic solution as follows,

    #"Added Custom" = Table.AddColumn(Source, "Custom",
        each [
            lst_delim = Text.Remove([Input], {"0".."9"}),
            lst_num = List.Select(Text.SplitAny([Input], lst_delim), each _<>"" and Text.Length(_)>4),
            result = Text.Combine(lst_num,"   ")
        ][result]
    )

[StephenF]

Yeah, Pats solution will cover 99.9% of cases, I just found one where it was an issue where there was both a 5 and a 7 digit number with only the 7 digit one being valid useful data.

I really just will have to have some error tolerance exception handling so it won't crash the query and the data will of course be stored as a text string only at all times.

[mahoneypat · Accepted solution]

You can add a custom column with this formula to get your desired result. 

Text.Combine(List.Select(List.Transform(Text.Split([Input], " "), each Text.Select(_, {"0".."9"})), each Text.Length(_) >=5), ", ")

Regards,

Pat