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.

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.

Oz Du Soleil
Latest posts by Oz Du Soleil (see all)


Denise McInerny, Excel Hot Tip, Power query, split columns, tables

You may also like

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

Free Power User Quick Guide

Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.