Excel has an interesting feature in the Data Tools Group of the Data Ribbon. It’s called Flash Fill and I believe it arrived in Excel 2013. You can use the keyboard shortcut Ctrl+E or click the Flash Fill icon:
Flash Fill is useful for splitting data into separate cells. Suppose your data has last and first names combined into a single cell and you want to split the data into last name/first name columns. In the table below, I’ve purposely used various delimiters like commas, semicolons, comma and space, semicolon and space, and only spaces to separate the names.
You could write very difficult formulas to check for the various delimiters and then separate the names accordingly, you could use Text to Columns (which alters your original data and will produce three different name columns for that last entry), or you can use Flash Fill. Please note that the columns to be filled must be next to the data you're splitting. In this case, the Last/First columns must be in Columns B & C.
Normally, you would begin in cell B2, type Brown, and press Enter, put the cursor back into cell B2 and click the Flash Fill icon
or press Ctrl+E. However, that would produce incorrect results with the name del Rey Sonya. So, we begin in cell B6 and type del Rey, capitalizing Rey, and pressing Enter. Then we click back into cell B6 and click the Flash Fill icon or press Ctrl+E. To split the first names into Column C, click into cell C2 or C6, Enter the appropriate first name, and initiate Flash Fill. Your data should look like this:
More tips
Flash Fill does not require you to select the data before splitting, as long as there are no empty rows in the data.
Flash Fill leaves the original data intact, so you can compare the results to the original data for accuracy.
In the example used here, I could have begun in Column C and then populated Column B.
Flash Fill works on a single column at a time