One of the best enhancements in Excel 2013, part of the newly-announced Office 2013 suite, is a simple option called Flash Fill. Rather than writing complex formulas to extract items of data from a single column, you can provide Excel with an example of what you want to do and it will copy it.
That’s more easily explained with an example. Suppose you had a list of names in the form Firstname Surname and you want to extract just the surname into an adjacent cell. To do that, you type the first name in the first couple of cells. You’ll see a greyed out version of what Excel thinks you want to do appear in the cells below. If that’s correct, type Control-E for Flash Fill (or select it from the Data tab). Excel will work out the process you’ve applied and continue that for the remaining cells in that column.
It isn’t a perfect solution for everything. If you have complex data where (for example) middle names appear sometimes but not all the time, you’ll need to use the existing data splitting functions. But for basic tasks it’s very impressive.