Splitting Columns without generating new column

• Split the column as you would normally
• Select that applied step and examine the Formula Bar:

Note that there is a "List" at the end of that function that names the two columns.

• In the formula bar, delete the second column name, and, optionally, change the first columns name

Voila!

If you need to do this 40 times, I would suggest NOT doing it from the User Interface, but rather going into the 

Advanced Editor and using the List.Accumulate function to repeat this for all the relevant columns. 

For Example:

Given:

and wanting to return just the numeric portion of all the columns, you can use this code in the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMqqorFLSUTIxNUtMSgYyDCtAXCMDk4qKCqVYnWglYyMTiIy5hWUFkDJLqgSSxgaGINHYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Test = _t, T2 = _t, T3 = _t, T4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", type text}, {"T2", type text}, {"T3", type text}, {"T4", type text}}),

//create list of columns to be split.
//Many ways to do this, but for all the columns:
    #"Columns to Split" = Table.ColumnNames(#"Changed Type"),

//Split all the columns:
    #"Splitted Columns" = List.Accumulate(
        #"Columns to Split",
        #"Changed Type",
        (s,c)=> Table.SplitColumn(s,c,Splitter.SplitTextByCharacterTransition(
                    {"0".."9"},(c)=>not List.Contains({"0".."9"},c)),{c}))
in
    #"Splitted Columns"

to get: