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 24 Excel Tips Guide

Discover the secrets to Excel efficiency with our free guide: '24 Proven Microsoft Excel Tips to Help You Save Time'

A humble gift from us to you, to make your Excel journey smoother and more productive. Grab your copy now and start mastering Excel like never before!