Split data into multiple columns (2024)

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 Home > Transform > Split Column.

2. Select Choose the 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.