Split column at first alpha character RRS feed

  • Question

  • I have a column that contains a code and description that I want to split into separate columns.  Here are a few examples:

    72 5 07 COM Medical Resources

    72 5 10 05 Practice

    72 5 30 40 12 In-House Nursing

    72 1* Administration and Support Services


    Basically I want to split at the first alpha character.  In the last example the * belongs with the code.

    Any help would be appreciated.

    Thursday, June 11, 2015 2:51 PM


  • 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):

        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"})
        #"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.

    Thursday, June 11, 2015 7:38 PM