# Calculate specific contents of a cell that contains a string of numbers and text

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.

• Please share a link to a sample spreadsheet with realistic data in it (at least five rows), along with the hand-entered results that you'd like to see a formula produce for that sample set. The closer to your real data you can make the sample data, the better. Jan 21 at 19:53
• Hi and welcome to WebApps. Such as: Finding "xAnotherExample" in Cell C1... - that sounds like a good approach. What result did you get when you tried that? Jan 22 at 2:45

Use `regexextract()`, like this:
``````=arrayformula(