Microsoft Office Forums - View Single Post - Unique, Sequential, Alpha-Numeric Number Generation for Projects

Alansidman · 08-03-2024, 03:49 PM

Using Power Query, here is the Mcode. If you add data to the source table and click on Refresh All on the Data Tab, it will automatically update the output. Try it on my sample attached.

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.1", "Column1.2"}),
    #"Sorted Rows" = Table.Sort(#"Split Column by Character Transition",{{"Column1.1", Order.Ascending}, {"Column1.2", Order.Ascending}}),
    #"Merged Columns" = Table.CombineColumns(#"Sorted Rows",{"Column1.1", "Column1.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
    #"Merged Columns"