Converting text to numbers with Copilot in Excel

[EricPatterson]

Greetings, This is the fourth in a series of posts that provides examples of what is possible with Copilot in Excel.  Today I will use a list of major league baseball ballparks in the United States.

Table with these columns: Name Capacity Location Surface Team Opened Distance to center field Roof type. First 2 rows of data: American Family Field‡ 41,900[1] Milwaukee, Wisconsin Grass Milwaukee Brewers 2001 400 feet (122 m) Retractable Angel Stadium 45,517[2] Anaheim, California Grass Los Angeles Angels 1966 396 feet (121 m) Open

I would like to fix up the Distance to center field column to remove the metric measurements so that it can be sorted correctly. 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:

Add a new column for center field distance, converting it to numbers and ignoring the part in parentheses.

Copilot in Excel looks at the content in the table and then suggests inserting a new calculated column that uses the Textbefore function to remove the extraneous information.

Looking at A1:H31, here's 1 formula column to review and insert in Column I: Center Field Distance Extracts the distance to center field in feet by converting the text value to an integer, providing a clear numerical representation of each stadium's center field distance. =INT(TEXTBEFORE([@[Distance to center field]], " feet"))

Hovering the mouse cursor over the "Insert column" 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 stadiums with a preview of the new center field distance column that would be added.

Clicking on the Insert Column button will accept the proposed change, inserting a new calculated column formula that extracts just the distance in feet, allowing me to sort the list correctly.

Picture showing the Excel workbook with copilot pane open. Includes the Stadiums table with Center field distance column 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.