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

 

[Rick Rothstein]

Try this in your copy of XL365...

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

 

[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)," ")))," ")))))

 

[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)?

 

[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!