Easy right? Um…

What I wanted:

I had a list of names in an Excel spreadsheet – both the first and last names were in column A, like this:

I wanted to split out the first name and last name, so that the first name appeared in column A and the last name appeared in column B, like this:

First I used Copilot’s Prompt Coach agent (You can find out more about Copilot agents here) to help me instruct Excel on what I wanted to do. I asked it:

I’m about to use this prompt in Excel – will it work? i have first names and last names in column A, how do i split this out so that first names are in column A and last names are in column b

The agent gave me a couple of options to answer my “how do I do this” question, plus a suggested prompt for Excel (the thing I actually asked for):

As you can see I wasn’t too far off with my first prompt attempt.

Off I went to ask Copilot in Excel to sort this out for me:

What I wanted was for it to make the changes to the document for me.

What it gave me were instructions on how to do it myself.

When I tried the instructions, I didn’t get the results I wanted. (I wanted to keep the rest of the data in my sheet but the ‘Text to Columns’ approach kept getting rid of all my other data.)

I probably could have plugged away at getting this correct, but it crossed my mind that by the time I’d figured that out, I probably could have gone through and manually moved all the data in the same amount of time!

What I ended up doing instead was pasting the list of names back into Copilot chat, asking it to reformat for me into two columns, and then copy pasting back into the Excel doc. Voila.

Lesson learned – keep things simple.