Using DAX to Split Delimited Text into Columns

Imagine that you've loaded a table into Power Pivot and it has a column called 'Full Name' which contains comma delimited surname and first name values such as 'Smith, John'. You'd like to split 'Smith' and 'John' into separate columns. Ideally, we would split these values into separate columns before loading the data into Power Pivot. This can normally be done using Power Query, or with some SQL if the data is being loaded from a SQL database. That said, I've seen a handful of scenarios where this wasn't an option. I've seen solutions for splitting text into columns using the MID and FIND DAX functions like this but there is another solution that makes it easier to 'pick' which part of the delimited text to return.