Converting Different Data Format into one Standardized Format

[ngiam]

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
AA4829	AA00004829
AA00001929	AA00001929
(AA00009401)	AA00009401
AA125	AA00000125

[Vijay_A_Verma · Accepted solution]

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")