Split data into multiple columns

Sometimes, data is consolidated into one column, such as first name and last name. But you might want separate columns for each. So, you can split the Sales Rep first name and last name into two columns.

1. Select the "Sales Rep" column, and then select Data. In the Get & Transform Data group, select From Table/Range.

2. In the Power Query Editor window, select Split Column > By Delimiter.

3. Select the default Each occurrence of the delimiter option, and then select OK.
   
   Power Query splits the Sales Rep names into two different columns named "Sales Rep 1" and "Sales Rep 2".

4. To change the default names, rename them to "Sales Rep First" and "Sales Rep Last".

5. To return the transformed data to the Excel worksheet, select Home > Close & Load.