Excel Copilot can write complex formulas, including ones that work with text. In this blog post, we’d like to demonstrate Excel Copilot’s ability to work with complex formulas with a dataset containing noteworthy individuals. 

Here’s what we will be showcasing:

Organize data by splitting a single column into multiple columns
Calculate age by extracting the years from a string of text

1. Organize data by splitting a single column into multiple columns

We have data that comes in this format: 

Alan Turing (1912-1954, United Kingdom):

However, we would like to make our table cleaner by splitting the data into multiple columns.

Alan Turing

1912-1954

United Kingdom

Here is the prompt we ask Copilot in Excel to help us with this task: “Split the first column into three: Name, Years alive, and Country. Insert after the first column.”

Copilot is able to take our natural language input, make an educated guess as to what part of the original column we mean by ‘Years alive’ for example, and perform the task by generating a formula that works on our data. Watch the video demo below.

2. Calculate age by extracting the years from a string of text

Now we have data that looks like this: 

Alan Turing
1912-1954
United Kingdom

George Takei
born 1937
United States

We want to ask Copilot to add a column with the individual’s age. In the end, we want something that looks like this: 

Alan Turing
1912-1954
42
United Kingdom

George Takei
born 1937
Alive today
United States

Prompt: Add a column that calculates how long each person was alive for. If they are still alive, write “Alive today”. 

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

Your feedback helps shape the future of Excel. Please leave a comment below with thoughts or questions on content related to this blog post. Additionally, please let us know how you like a particular feature and what we can improve upon—“Give a compliment” or “Make a suggestion”.  You can also submit new ideas or vote for other ideas via Microsoft Feedback.

Subscribe to our Excel Blog and the Insiders Blog to get the latest updates. Stay connected with us and other Excel fans around the world – join our Excel Community and follow us on X, formerly Twitter.

​Microsoft Tech Community – Latest Blogs –Read More