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

[amolvijay]

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>

 

[VBA Geek]

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]

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

 

[Vidar]

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]

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]

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

 

[Vidar]

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.