Excel Sum extracted numbers from text string

Question

Somebody could help me with this problem? I need to extract the numbers from a text string and then Sum them with a single formula.

The 3 first caracters of the string are always the same:

A::23

A::4

A::8

A::10

A::44

To remove the alphanumeric caracters I use =MID(A1,3,2), however this formula works well for every single cell only.

I am trying to create a formula that will Sum all the extracted numbers of a given range.

Any suggestion?

CallumDA CallumDA · Accepted Answer · 2014-09-14T17:31:53

This is one way to do it:

{=SUM(VALUE(MID($A$1:$A$5,4,2)))}

to enter the function press Ctrl+Shift+Enter (rather than just Enter)

Edit:

{=SUM(MID($A$1:$A$5,4,2)*1)}

also works