excelexcel-formula

How do I pull numbers after specific letter from varying strings within cell range and sum them?

Trying to pull numbers that follow after a specific letter and sum them. How do I do that ?

For instance, all that come after C (=200+15)
enter image description here

Using LEN, SEARCH as per below gives VALUE error

=SUM(MID(AS16,SEARCH("C:",AS16)+2,LEN(AS16)-SEARCH("C",AS16)))
Solution

  • Here are two alternative workarounds, if one don't have access to REGEX yet in MS365

    enter image description here

    • Formula used in cell B2

    =SUM(--IFNA(TEXTBEFORE(TEXTAFTER(A1:A4,"C:")&CHAR(10),CHAR(10)),0))

    • Or, using TEXTJOIN() to combine all the data into one and split using TEXTSPLIT() which I have already commented in the Staging Ground comments thread:

    enter image description here

    =LET(
     a, IFNA(TEXTSPLIT(TEXTJOIN(CHAR(10),1,A1:A4),":",CHAR(10)),0), 
     SUM((TAKE(a,,1)="C")*DROP(a,,1)))