Separate text and number

[ridwanr]

Hi everyone. Just wanted to know how do we separate text and numbers in several rows if possible by making use of an excel formula.

Plz see attached example for better understanding.

Thanking you in advance for your help.

Kind regards,
Ridwan

 

[AlanSidman]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "Name", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Name.1", "Name.2"})
in
    #"Split Column by Character Transition"

 

[bosco_yip]

Formula solution

In C2, formula copied down :

=LOOKUP(9^9,0+RIGHT(A2,ROW($1:$99)))

In B2, formula copied down :

=SUBSTITUTE(A2,C2,"")