0

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?

2
  • 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.
    – Erik Tyler
    Jan 21, 2022 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?
    – Tedinoz
    Jan 22, 2022 at 2:45

1 Answer 1

1

Use regexextract(), like this:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.