Forum Discussion

EricPatterson's avatar
EricPatterson
Icon for Microsoft rankMicrosoft
Sep 05, 2024

Converting Text to Numbers using Copilot for Excel

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 co...
copilot in excel
excel
Patrick2788's avatar
Patrick2788
Silver Contributor
Sep 05, 2024

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)
)

Resources