Show
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 Downloadspq-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. Tags Denise McInerny, Excel Hot Tip, Power query, split columns, tables You may also likeFree Power User Quick GuideDiscover 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.
Split First And Last Name FormulaBy Neale Blackwood Assuming the name is in cell A1 the following formulae will extract the first and second names: First name formula
=LEFT(A1,SEARCH(“,”,A1)-1) Second name formula =TRIM(RIGHT(A1,LEN(A1)-SEARCH(“,”,A1))) You could also use Excel’s ‘Text to Columns’ feature in the Data menu. This allows you to split the contents of cells based on commas, spaces, semi-colons or another character that you specify. In the above example you would select the range that had the names then click the Data menu, select ‘Text to Columns’, ensure the Delimited option is selected and click ‘Next’. Select the ‘Space’ and ‘comma’ options and Click ‘Next’. Select the ‘Space’ and ‘Comma’ options and Click ‘Next’ and ‘Next’ again. ExtrasWhen using the Text To Columns feature it is a good idea not to have entries in cells to the right of the column being split as they may be overwritten. In Excel 2007 the Text To Columns icon is in the Data Ribbon tab in the Data Tools section. Related Posts:Share This Story, Choose Your Platform! |
