How can I sum the values from a regexextract on multiple rows

Question

I have 3 rows. All of them have a (number)&(letter) format. For example, Say 5&b is present on 3 rows. My question is:

How do I do sum just the numbers? I have tried:

  =SUM(REGEXEXTRACT(B3:B, "(.)&."))

I am trying to add the first value of all the rows.

Answer 1

Try:

  =ARRAYFORMULA(SUM(--REGEXEXTRACT(B3:B27, "(\d+)&\w+")))

• \d+ stands for one or more digits. \w+ will stand for one or more words.
• You must use ARRAYFORMULA to extract data from the whole array B3:B27.
• -- will convert text to number