Forum Discussion

ngiam's avatar
ngiam
Icon for Helper I rankHelper I
4 years ago
Solved

Converting Different Data Format into one Standardized Format

Hi All,   How do I convert raw data with the following discrepency into one standardized format?   Thank you in advance! Example;   Compiled Data Standardized Format -> AA0000XXXX AA482...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    In a custom column, use following formula

    =Text.Select([Compiled Data],{"A".."Z","a".."z"})&Text.PadStart(Text.Select([Compiled Data],{"0".."9"}),8,"0")
Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

In a custom column, use following formula

=Text.Select([Compiled Data],{"A".."Z","a".."z"})&Text.PadStart(Text.Select([Compiled Data],{"0".."9"}),8,"0")