Converting Text to Numbers using Copilot for Excel

[EricPatterson]

Greetings, We’re continuing our series of posts to show you some of the things that are possible to do with Copilot in Excel. Today I have a table that shows all of the employee travel for my team coming up. I would like to get a total count of people traveling.

A table with 15 rows with columns for Travel dates and Person(s) Traveling. The Person(s) Traveling column has some cells with multiple names, separated by different delimiters.

I ask Copilot the following to help me answer my question:

Add a column to calculate the number of people travelling based on the Person(s) Traveling column

Copilot in Excel pane showing the above prompt and the suggested formula column to calculate the number of people.

Looking at K2:L17, here's 1 formula column to review and insert in Column M:

Number of People

Counts the number of people traveling by identifying and summing the occurrences of delimiters such as "+", ";", and "," in the "Person(s) Traveling" column, then adding 1 (person) to account for the first person listed.

=SUM(LEN([@[Person(s) Traveling]])-LEN(SUBSTITUTE([@[Person(s) Traveling]],"+","")),LEN([@[Person(s) Traveling]])-LEN(SUBSTITUTE([@[Person(s) Traveling]],";","")),LEN([@[Person(s) Traveling]])-LEN(SUBSTITUTE([@[Person(s) Traveling]],",","")))+1

I hover over the Insert Column button to see how it will calculate the results. With a quick spot check, I see that the numbers look right except for the 2^nd row that uses a slash as a delimiter.

Travel table with a preview of the suggested Number of People column that will be added. The 2nd row that looks incorrect is highlighted.

Since the formula doesn’t calculate all of the rows correctly, I prompt Copilot again with the following:

That looks mostly right, please update to also use the / as a delimiter

Copilot pane with the above prompt and an updated formula that included the slash character.

Looking at K2:L17, here's 1 formula column to review and insert in Column M:

Number of People

Counts the number of people traveling by identifying and removing common delimiters such as "+", ";", ",", and "/" from the "Person(s) Traveling" entries, then calculating the difference in length to determine the number of individuals listed.

=LEN([@[Person(s) Traveling]])-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@[Person(s) Traveling]],"+",""),";",""),",",""),"/",""))+1

The updated formula looks correct. After clicking on the Insert Column command, I get the following table:

Completed travel table showing Travel Dates, Person(s) Traveling and a new calculated formula column for the number of people.

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 

I re-created your table in Excel because I was curious what formula Copilot would suggest. I used the same wording for the prompt, too.

Copilot suggested:

=COUNTA(
    FILTERXML(
        "<t><s>" &
            SUBSTITUTE(
                SUBSTITUTE(
                    SUBSTITUTE([@[Person(s) Traveling]], ",", "</s><s>"),
                    ";",
                    "</s><s>"
                ),
                "+",
                "</s><s>"
            ) & "</s></t>",
        "//s"
    )
)

A more direct solution would be:

=LET(
    split, TEXTSPLIT([@[Person(s) Traveling]], {"+", "/", ";", ","}),
    COUNTA(split)
)