cancel
Showing results for
Did you mean:
Resolver I

## Isolating 5 digit+ Numbers in Edit Query/Advanced editor

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
1 ACCEPTED SOLUTION
Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

3 REPLIES 3
Super User III

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]
)``````

Resolver I

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.

Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Announcements