How to remove any non-numerical characters

[Anonymous]

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

[DataInsights · Accepted solution]

@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 · Accepted solution]

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)

[Anonymous]

Perfect, thank you!

[DataInsights · Accepted solution]

@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)
      }
    )

[Anonymous]

This also works, thank you!