March 29

Power Query – Splitting Names From Suffixes


Power Query – Splitting Names From Suffixes

Splitting Names From Suffixes

Data cleansing and prep can come to you in any manner and that’s why I often compare it to bull-riding: you’ve got to get on and deal with whatever happens.

In this video, we need to split the names apart from suffixes like: III, DDS, EdD, etc. This happens when you need a list for one purpose but the best source data that you can get your hands on was used for a very different purpose.

PQ Ex.

There are plenty of ways to split the names from the suffixes. Text-to-Columns is the first that comes to mind, however, it will create a mess (as I describe in the video).

Power Query’s ‘split column’ feature is the way to go! We can tell Excel to split the column at the very first comma.

Also, check out Denise McInerny’s comment about the synergy between Excel users and the SQL community. We’re all having to deal with challenges like this. So, check it out and save yourself some time and heartache.

2015-02-03_20-55-27Oz Shows you How To Do It

Bonus Downloads

pq-column-splitting-exceltv-example <—– Download the file that Oz used in the video

What’s next?

Have you tried Power Query?  Need some guidance?  Leave your comments below.

Loved this? Spread the word

About the Author

Oz is an Excel MVP, author of "Guerrilla Data Analysis, 2nd Ed." He's a lover of bowties and ghost pepper sriracha. Oz operates out of Portland, Or where is mission is to make data analysis accessible to those who cry out, "I don't even know where to start!"

Oz Du Soleil

Related posts

How to create a relative file path in Power Query

Read More

Make Your Excel Spreadsheet into a Bitmap Image With This Excel Magic Trick

Read More

Using Power Query to Connect Tables for Reporting | Excel.TV, Episode 55

Read More

Advanced Excel Tutorial – VLOOKUP() Speed in Excel | Excel TV, Episode 54

Read More
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Subscribe to our newsletter now!