One solution would be to create two custom columns - one that holds the code only, and one that holds the description only. The following script was generated after I created a query from your sample data copied to an Excel table named Table2 ("Text"
being the column name):
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Code", each
Text.Trim(Text.Remove([Text],{"A".."z","-"}))),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Description", each
Text.Trim(Text.Remove([Text],{"0".."9","*"}))),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Text"})
in
#"Removed Columns"
The two custom columns added are in bold (actual formulas underlined). In one custom column, all alpha text (plus "-") is removed and the result trimmed. In the other custom column, all numeric text (plus "*") is removed and the result
trimmed.
The Text.Remove function takes a text string, and a list of characters (or a single character) to remove.
Note that when you specify a list like {"A".."z"}, M returns all ANSI characters between A and z (all upper and lowercase characters, plus [,\,]^,` characters. The hyphen isn't included, so it's added to the list separately. Similarly,
{"0".."9"} returns all numbers between 0 and 9 as text.
