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.
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.
- 01: Data Perspectives From An Unwitting Analyst - January 28, 2016
- Introduction – Excel In The Wild - December 6, 2015
- Oz’s Memories from PASS BAC 2015 - May 7, 2015
