Surendra Reddy on Linked posed a challenge on how to Clean & Split Dirty Data as shown below

There are many ways of doing this but I find it easy to use below Power Query M code

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
clean = Table.AddColumn(Source, "clean", each Text.Select([Messy Data],{"a".."z","A".."Z","0".."9"})), split = Table.SplitColumn(clean, "clean", Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}), {"First", "Last"}) 

See the below video for an explanation


Do you want to excel in Excel?


Seeking Microsoft Certification?

Every Week Receive Resources To Get you Closer to Your Goal!

No Huff! No Fluff! No Spam!

You are on your way to Excelling in Excel and Becoming Microsoft Certified!

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.