Forum Discussion
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 |
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
3 Replies
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
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] )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.
