# Sum the numeric part of cells that contain specific strings

I am struggling to find a way to sum the numeric part of cells that contain specific strings. Let's say that: cell A1: `pa 23.45`, cell A2: `ts 12.35`, cell A3: `pa 10.55`.

The result of the sum of A1:A3 I would like to be `34` (23.45+10.55), which is the sum of only A1 and A3 because they contain `pa`.

So, find the cells in a range that contains `pa` and sum the numeric part of them.

I don't want to have separate cells for string and numeric part.

``````=ArrayFormula(sum(value(IFERROR(REGEXEXTRACT(A1:A3,"pa ([^/d]*)"),))))
``````

# Explanation

• `REGEXEXTRACT(A1:A3,"pa ([^/d]*)")` returns the text at the right of `pa` that starts with any digit, if a matching string is not found, then returns an error.
• `IFERROR` replaces the error result by a blank.
• `value` convert the strings to numbers if the string is a valid number.
• `sum` sums the values.
• `ArrayFormula` makes the result of the above calculation to be expanded to the required area.

Alternative:

``````=ArrayFormula(sum(iferror(value(1*substitute (A1:A,"pa ","")))))
``````

[In other words, I failed to spot this was yet another bump rather than a new post :) This formula is slightly shorter though.]