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

 

[Vector8086]

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

I'm just reading the product details on the computer I'm currently using.

 

[Vector8086]

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

Thanks! I just updated it.

 

[Vector8086]

Try this in your copy of XL365...

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

This does the job in XL365. If it could take in a cell range, like A1:A2 or (A1,A3,A5) that would be awesome.

 

[Rick Rothstein]

This does the job in XL365. If it could take in a cell range, like A1:A2 or (A1,A3,A5) that would be awesome.

The following formula can do a contiguous range (such as A1:A5), but I it cannot (and I don't think it is possible) do a discontiguous range (like A1,A3,A5)

=MAP(A1:A5,LAMBDA(x,IF(x="","",SUM(0+TEXTSPLIT(TRIM(CONCAT(IFERROR(0+MID(x,SEQUENCE(LEN(x)),1)," ")))," ")))))

 

[Vector8086]

Try this in your copy of XL365...

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

Sticking with XL365 I could use the TEXTJOIN function to expand this formula from a single cell to a list of ranges. I also added a dummy "0," to cover the case of a single blank cell.

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

 

[Rick Rothstein]

Sticking with XL365 I could use the TEXTJOIN function to expand this formula from a single cell to a list of ranges. I also added a dummy "0," to cover the case of a single blank cell.

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

Did you see my last post (posted 2 to 3 minutes before your post above)?

 

[Vector8086]

The following formula can do a contiguous range (such as A1:A5), but I it cannot (and I don't think it is possible) do a discontiguous range (like A1,A3,A5)

=MAP(A1:A5,LAMBDA(x,IF(x="","",SUM(0+TEXTSPLIT(TRIM(CONCAT(IFERROR(0+MID(x,SEQUENCE(LEN(x)),1)," ")))," ")))))

This returns the individual 5 sums from A1:A5. It ends up taking 5 spaces, and not combining the sums. Your original formula works better for me.

 

[Vector8086]

Did you see my last post (posted 2 to 3 minutes before your post above)?

I just tried it, but the first formula is better.

 

[Rick Rothstein]

I just tried it, but the first formula is better.

Which first formula... mine? If so, the last formula I posted is my first formula, just bundled in other function calls so it can process all cells in a contiguous range at the same time instead of one at a time like my first formula.

 

[Vector8086]

Which first formula... mine? If so, the last formula I posted is my first formula, just bundled in other function calls so it can process all cells in a contiguous range at the same time instead of one at a time like my first formula.

Yes. A modified version of it:
=SUM(0+TEXTSPLIT(TRIM(CONCAT(IFERROR(0+MID(TEXTJOIN(",",TRUE,A1,A3,"0,"),SEQUENCE(LEN(TEXTJOIN(",",TRUE,A1,A3,"0,"))),1)," ")))," "))