Using Copilot in Excel to split columns

[EricPatterson]

Hi Everyone, this is the first in a series of posts to show you some of the things that are possible to do with your workbooks using Copilot. Today I will start with this list of employees:

Table with these columns: Name Address City State. First two rows of data are: Claude Paulet 123 Main Avenue Bellevue Washington Jatindra Sanyal 1122 First Place Ln N Corona California

I would like to have the names in this list separated into 2 columns for the first and last names. To accomplish this, I'll start by clicking on the copilot button on the right side of the Home tab, showing the copilot pane and type the prompt:

Split the name column into first and last name

Copilot in Excel looks at the content in the list and then suggests inserting 2 new calculated column formulas to split the first and last names from the Name column.

Looking at A1:D17, here are 2 formula columns to review and insert in Columns E and F: 1. First name Extracts the first name of each individual by splitting the full name at the space and selecting the first part. =INDEX(TEXTSPLIT([@Name]," ",,TRUE),1,1) Show explanation 2. Last name Extracts the last name of each individual by splitting their full name and selecting the last part. =INDEX(CHOOSECOLS(TEXTSPLIT([@Name]," ",,TRUE),-1),1,1)

Hovering the mouse cursor over the "Insert columns" button in the copilot pane shows a preview of what inserting the new column formulas will look like.  From the preview, it looks like it is doing what I wanted.

Picture of the list of employees with a preview of 2 new columns that would be added. First name column is being shown in column E and Last Name in column F.

Clicking on the Insert Columns button will accept the proposed change, inserting 2 new columns with calculated column formulas that split out the first and last names, giving me the result I was looking for!

Picture showing the Excel workbook with copilot pane open. Includes the employee table with 2 new columns added.

Over the coming weeks I will be sharing more examples of what you can do with Copilot in Excel.

Thanks for reading,

Microsoft Excel Team

*Disclaimer: If you try these types of prompts and they do not work as expected, it is most likely due to our gradual feature rollout process. Please try again in a few weeks.

[#2]

@EricPatterson 

Picture showing Excel Copilot pane containing this text:....

Actually screenshot contains another text and formulae

[#3]

Thanks! Corrected.