Forum Discussion

ngiam's avatar
ngiam
Helper 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 DataStandardized Format -> AA0000XXXX
AA4829AA00004829
AA00001929AA00001929
(AA00009401)AA00009401
AA125AA00000125
  
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")

1 Reply

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most 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")