I have a sheet that contains cells that have lists of data within them, for example: One cell contains: 1xExample, 2xAnotherExample, 5xThirdExample The cell below contains: 1xAnotherExample, 3xThirdExample, 2xExampleFour And so on..
If I want to calculate the total of "AnotherExample", which in the two example cells above would be 3 then how would I go about doing this? 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 specific text and then lookup the number before that string of text and add it together?
Such as: Finding "xAnotherExample" in Cell C1, look 1 character left, get number. This way it would ignore the other values in the cell and only get the one I want, then add that from each cell containing "xAnotherExample" and output the total.
Any help would be much appreciated.