I have cells that contain multiple strings delimited by commas.
Each string is comprised of the following 3 elements joined together:
- A
number
- An
x
- 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?