Sum all numbers prepended to text that matches a search string

Question

I have cells that contain multiple strings delimited by commas.

Each string is comprised of the following 3 elements joined together:

1. A number
2. An x
3. A substring

For example, 1xExample, 2xExample, 2xAnotherExample, 5xThirdExample, etc.

I want to add the number from each string that contains a particular substring.

Sample Data

	A
1	1xExample, 2xAnotherExample, 5xThirdExample
2	1xAnotherExample, 3xThirdExample, 2xExampleFour

For example, using the sample data above, if the substring I'm trying to match is AnotherExample then the formula would return 3. AnotherExample appears once in A1 preceded by a 2 and it also appears once in A2 preceded by a 1. So adding the numbers from those two strings gives 2+1=3.

I've tried a few different things and the closest I can get is a formula which takes all numbers in the cell and adds them together instead of taking a specific number.

Is there a way for a formula to find all the strings that contain a substring and then take the number from each of those strings and add them together?

Answer 1

Use regexextract(), like this:

=arrayformula( 
  sum( 
    iferror( 
      regexextract(A2:A, "(\d+)xAnotherExample") + 0 
    ) 
  ) 
)