Forum Discussion
How to sum the numbers in a text string?
Hi If I have a text string in a cell below: "Path 8.5+Metrocard 10" And I would like to return the result of the sum of the numbers 8.5+10 "18.5" (see attached image) How can I do that by using ...
This is very close to Patrick2788. We must go to the same Excel school!
= LET(
array, TEXTSPLIT(target, " ", "+", 1),
value, VALUE(DROP(array,,1)),
SUM(value)
)
I have used the TEXTSPLIT to generate a two column array rather than a single row. The text column could be used for further filtering if required.
Filtering on "Path" might be
= LET(
array, TEXTSPLIT(target, " ", "+", 1),
text, TAKE(array,,1),
value, VALUE(DROP(array,,1)),
SUM(FILTER(value, text="Path"))
)
This request seems likes ages ago! Going back to the original request without path criteria:
=LET(
split, WRAPROWS(TEXTSPLIT(A1, {" ", "+"}), 2),
SUM(TAKE(split, , -1) * 1)
)I hadn't realised just how old the discussion is; answers now would not be of much value to the OP!
On the other hand, past discussions can provide a rich vein of problems that I would never think of. They allow me to evaluate and develop ways of working with modern Excel that bear little resemblance to past practice. Usually the new approaches are far better, but its not always the case.