Extracting numbers from a string and adding them up

[Vector8086]

I saw an old post where with the below formula to extract numbers from a string containing alphanumeric values - something like "p2, e(3), 15ip"

This will return: 2315.

My question is, can this be modified to return the sum of these numbers, i.e. 20? And can A1 be a range of cells where all the resulting numbers are added up?

Code:
B1: =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

Thanks in advance!
V

 

[Legacy 143009]

Hi,

Like this?

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)+0)

 

[ISY]

Hi

=SUM(--TEXTSPLIT(TRIM(CONCAT(TEXT(MID(A1,SEQUENCE(99),1),"0,,0, ")))," "))

 

[Vector8086]

Hi,

Like this?

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)+0)

Hi! Thanks for the help. This one is not adding the last number properly. I'm getting 11. It looks like it's adding only the single numbers. The sum should be 2+3+15 =20.

 

[Vector8086]

Hi

=SUM(--TEXTSPLIT(TRIM(CONCAT(TEXT(MID(A1,SEQUENCE(99),1),"0,,0, ")))," "))

Thank you for the formula. My version of Excel (2016) doesn't seem to include the SEQUENCE function. So, I'm getting an error.

 

[Legacy 143009]

Aahh you are looking for 15, not 1+5. Sorry. I don't know. Maybe you should wait for other replies.

 

[Vector8086]

Aahh you are looking for 15, not 1+5. Sorry. I don't know. Maybe you should wait for other replies.

Right. The numbers that are together have to be treated as a single value. Thanks for the help though!

 

[dreid1011]

Thank you for the formula. My version of Excel (2016) doesn't seem to include the SEQUENCE function. So, I'm getting an error.

You should update your profile to reflect the version of Excel you are using.

 

[Rick Rothstein]

Try this in your copy of XL365...

=SUM(0+TEXTSPLIT(TRIM(CONCAT(IFERROR(0+MID(A1,SEQUENCE(LEN(A1)),1)," ")))," "))

 

[Rick Rothstein]

Thank you for the formula. My version of Excel (2016) doesn't seem to include the SEQUENCE function. So, I'm getting an error.

Your profile is showing you have XL365, XL2021 and XL2007... where did the XL2016 come from????