# Extract Numbers from String in a column and sumup excluding "-" cells

#### amolvijay

##### Board Regular
Hi Team,

Need your help. I have data set in a column where I have strings ending with two digit numbers. I wish to extract all the digits in that column and sumup in a cell. Apart from strings the column may contain "-", excluding/ignoring total should be calculated...

 Total 282 Src/Prc-23 Src/Prc-23 Mngr-23 Engg-23 Engg-24 Engg-25 Engg-26 Src/Prc-23 Src/Prc-23 - - - Mngr-23 Mngr-23 Mngr-23

<colgroup><col><col></colgroup><tbody>
</tbody>

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### VBA Geek

##### MrExcel MVP
from the data shown above, assuming your data goes from b1 to b15 then

=SUMPRODUCT(IFERROR(MID(B1:B15,SEARCH("-", B1:B15)+1, LEN(B1:B15) - (SEARCH("-", B1:B15)-1))+0,0))

CNTR SHIFT ENTER when your enter the formula

#### dispelthemyth

##### Well-known Member
An alternative to VBA Geeks great solution

<Code>=SUM(IFERROR(MID(B2:B16,FIND("-",\$B\$2:\$B\$16,1)+1,LEN(\$B\$2:\$B\$16)-FIND("-",\$B\$2:\$B\$16,1))*1,0))</Code>

Press Ctrl + Shift + Enter

The \$B\$2:\$B\$16 just needs changing to the range you are looking at

Last edited:

#### Vidar

##### Well-known Member
Another solution:
=SUM(IF(ISNUMBER(RIGHT(B2:B16,2)+0),RIGHT(B2:B16,2)+0))

It's an array formula and must be confirmed with CTRL+Shift+Enter

#### dispelthemyth

##### Well-known Member
Another solution:
=SUM(IF(ISNUMBER(RIGHT(B2:B16,2)+0),RIGHT(B2:B16,2)+0))

It's an array formula and must be confirmed with CTRL+Shift+Enter

Great and simple solution if the data is consistent with having the final 2 characters having the numbers (unless empty), and if thats the case it could be simplified even more

=SUM(IFERROR(RIGHT(\$B\$2:\$B\$16,2)+0,0))
Then Ctrl + Shift + Enter as its an array formula

amolvijay

Hopefully one of these solutions meets your current and future needs

Graeme

#### amolvijay

##### Board Regular
Hi All...
Thank you very much...yes it worked..thank you...You saved by day...

#### Vidar

##### Well-known Member
Great and simple solution if the data is consistent with having the final 2 characters having the numbers (unless empty), and if thats the case it could be simplified even more

=SUM(IFERROR(RIGHT(\$B\$2:\$B\$16,2)+0,0))
Then Ctrl + Shift + Enter as its an array formula

amolvijay

Hopefully one of these solutions meets your current and future needs

Graeme

The OP didn't post which Version of Excel he/she is using. The IFEEROR function came in 2007.
But apparently it worked, so IFERROR is a simpler way to go.

Replies
3
Views
359
Replies
23
Views
684
Replies
9
Views
167
Replies
4
Views
231
Replies
3
Views
307

1,195,670
Messages
6,011,074
Members
441,581
Latest member
rp4717

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back