Summing Numbers in Cells Including Consecutive Numbers

[NeilsonWheels]

Hi All,

I want to take a cell (A1) with a value such as "M12G3" and sum the values of these numbers. The answer to this problem would be 15 (12+3) and not 6 (1+2+3)

I got as far as [=SUM(IFERROR(VALUE(MID(A1,SEQUENCE(LEN(A1)),1)),0))]. This gives the incorrect answer and cant handle consecutive numbers :(.

This solution can't use vba/macros. I'm not sure where to go from here

[Fluff13]

How about

Please Login or Register  to view this content.

[AliGW]

Welcome to the forum.

What exactly do you mean by 'consecutive numbers'? Can you give an example?

If you mean that something like M123G should be seen as 12 and 3, then there's no way that Excel could determine that.

[NeilsonWheels]

This works fantastically! I can also confirm the following work for this:
=SUM(VALUE(TEXTSPLIT(TEXTJOIN(,, IFERROR(VALUE(MID(A1, SEQUENCE(1, LEN(A1)), 1)), "|")),, "|", 1))) - Building on my own attempt
=SUM(--TEXTSPLIT(A1, TEXTSPLIT(A1, SEQUENCE(10) - 1, , 1), , 1)) - Similar approach to your own

Surprising there are so many ways!

[NeilsonWheels]

See fluff13's response he has managed to find a solution to this! Note my example was slightly different to yours. "M12G3" not "M123G"

[Fluff13]

Glad to help & thanks for the feedback.