Summing numbers in a single cell - Excel

Question

Cell E5 contains 5v5+4. I want cell F5 to sum the numbers in cell E5, to give 14.

How can I do that?

I have no idea how to do that, so didn't try anything.

For context, I this is for a football drill project where I need both the total number of players and the breakdown of players in each team.

Answer 1

Try using TEXTSPLIT() function:

---

=SUM(--TEXTSPLIT(F5,"+",CHAR(SEQUENCE(26,,97)),,,0))

---

Or,

=SUM(--TEXTSPLIT(F5,"+",CHAR(SEQUENCE(26,,65)+{0,32}),,,0))

---

The second approach ensures to exclude any alphabets whether small or caps, along with + operator to return an array of numbers as text, therefore on adding a double unary converts numbers returned as text to actual number for math operation. For converting the same, using +0 or /1 or *1 can also be used.

---

Or as mentioned by JvdV Sir, using nested TEXTSPLIT()

=SUM(--TEXTSPLIT(E5,TEXTSPLIT(E5,ROW(1:10)-1,,1)))

---