Split last characters before the number from column

[rajrajsha]

Hi Team,

I'm trying to split a column that contains text and number into two seperate columns, one containing the number and other contains the character.

Below is the sample data how it looks.. I need to extract numbers and text seperately..

Please ignore if the text character is in the middle of the string. (Ex. 170M01788) 

source	Number	Text
170M01830X	170M01830	X
016 260423XXX	016 260423	XXX
170M01788	170M01788
170M02313X	170M02313	X
002P113748	002P113748
016 273150AM	016 273150	AM

Number and Text columns is what im expecting.

Thanks..

[Fowmy · Accepted solution]

@rajrajsha 

You can do it two steps:
Choose Digit to Non Digit

Then choose Merge Columns under Trasnform tab after selecting 1st and 2nd columns

Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ38DUwtDA2iFCK1YlWMjA0UzAyMzAxMo6IgIhAFJhbWCB4RsaGxlDlBkYBhobG5iYWCN3mxoamBo6+SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [source = _t]),
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "source", 
Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"source.1", "source.2", "source.3"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition",{"source.1", "source.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Source")
in
    #"Merged Columns"

Result

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ38DUwtDA2iFCK1YlWMjA0UzAyMzAxMo6IgIhAFJhbWCB4RsaGxlDlBkYBhobG5iYWCN3mxoamBo6+SrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [source = _t]),
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "source", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c))),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition",List.RemoveLastN(Table.ColumnNames(#"Split Column by Character Transition"),1),Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Number"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{List.Last(Table.ColumnNames(#"Merged Columns")), "Text"}})
in
    #"Renamed Columns"