A new feature in Excel 2013 called Flash Fill allows the user to separate or combine data within entire columns based on the pattern that Excel picks up from the other columns. You can also use Flash Fill to change the case of text. As long as Excel can pick up a pattern, it will work to complete the column for you.
Note the first and last name columns below:
Suppose you would like to join these columns so that the full name will appear in a separate column. To make the new column with the full name, insert the cursor in the third column and type the first and last name for Bud Roberts. Press ENTER and begin typing the second name. You will see a transparent menu with a preview of suggested names:
Press ENTER to finish the column. You can also use the Flash Fill command on the DATA tab of the Ribbon. In this example, the First Name and Last Name columns do not have to be adjacent to each other, but the column using the Flash Fill must be next to one of these columns for Excel to recognize the correct pattern. This same technique works when separating text within columns:
The data must be entered consistently; for example, if some first names had a middle initial and some did not, you might get some unexpected results. If you did not want to use the Flash Fill, press the ESC key and keep typing to ignore the transparent drop-down list.
NOTE: Even though the “Convert Text to Columns Wizard” can still be used to separate text into columns, and the CONCATENATE function can be used to join text, the Flash Fill method is much quicker and avoids placing any formulas within the cells.