Forum Discussion
How to remove any non-numerical characters
Found a couple of answers on this but none that fit my issue - I'm working with data that's been entered into a free text field, and it's a bit of a nightmare. The easiest way to clean it up will be to strip out any letters, punctuation or spaces etc and leave only the numerical values, and then I can look at only the left 9 characters. Can this be done in a calculated column? Below is an example of the sort of mess I'm working with, they should all be in the same format as the first line!
| DT Number |
| 817754788 |
| 817754749; ZLODT 817754832 |
| 817751095/817751096 |
| 817754865, 817754869, 817755578 |
| 817754898 817754906 |
| ZLO DT 817792131 |
Thanks
Anonymous,
In Power Query, create this custom column. The ranges such as 32 - 47 refer to ASCII characters.
Text.Remove( [DT Number], { Character.FromNumber(32) .. Character.FromNumber(47), Character.FromNumber(58) .. Character.FromNumber(255) } )In PQ, in a custom column, you can use below formula
= Text.Select([DT Number],{"0".."9"})If you want to look at only first 9 characters of extracted numbers
= Text.Start(Text.Select([DT Number],{"0".."9"}),9)
4 Replies
- DataInsights
Super User
Anonymous,
In Power Query, create this custom column. The ranges such as 32 - 47 refer to ASCII characters.
Text.Remove( [DT Number], { Character.FromNumber(32) .. Character.FromNumber(47), Character.FromNumber(58) .. Character.FromNumber(255) } ) - Vijay_A_Verma
Most Valuable Professional
In PQ, in a custom column, you can use below formula
= Text.Select([DT Number],{"0".."9"})If you want to look at only first 9 characters of extracted numbers
= Text.Start(Text.Select([DT Number],{"0".."9"}),9)