text and numbers in same cell, sum of numbers ?

[skgurmeet]

Dear All,

i have query, please help out.

multiple numbers and text are in single cell which are separated through spaces or comma, is there any formula which can bypass text and add all the numbers in the cell.

For example:

<table style="width: 384px; height: 83px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 125pt;" width="167"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 125pt;" height="20" width="167">red 12, yellow 10, green 5</td> <td class="xl63" style="border-left: medium none; width: 48pt;" align="right" width="64">27</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">red 3, blue 9</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">12</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; border-top: medium none;" height="20">yellow 21, blue 3</td> <td class="xl63" style="border-top: medium none; border-left: medium none;" align="right">24</td> </tr> </tbody></table>
Thanks,
Gurmeet

 

[Marcol]

Try

=SUMPRODUCT(1*MID(A1:A3,FIND(" ",A1:A3),255))

 

[T. Valko]

<TABLE style="WIDTH: 273px; HEIGHT: 113px" cellSpacing=0 cellPadding=0 border=1><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center"><TD>

</TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">1</TD><TD>red 12</TD><TD style="TEXT-ALIGN: right">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">2</TD><TD>blue 9</TD><TD style="TEXT-ALIGN: right">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">3</TD><TD>red 3

</TD><TD style="TEXT-ALIGN: right">

</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: rgb(202,202,202); TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">24

</TD><TD style="TEXT-ALIGN: right">

</TD></TR></TBODY></TABLE>

A4 is =Sumnums(A1:A3)

Try this...

Book1
	A
1	red 12
2	blue 9
3	red 3
4	_
5	24
Sheet1

This array formula** entered in A5:

=SUM(--MID(A1:A3,FIND(" ",A1:A3),15))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[ragweed]

Thanks for getting back right away. That works great. Some of the cells in this example are blank and some contain only text.
<table style="width: 67px; height: 172px;" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="text-align:center; border-style:solid; border-width:1px; border-color:#000000; ">red 12</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">blue
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">red 2
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="color:#ffffff; text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">_</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:center; border-right-style:solid; border-bottom-style:solid; border-left-style:solid; border-width:1px; border-color:#000000; ">14
</td></tr></tbody></table>A5=SUM(--MID(A1:A4,FIND(" ",A1:A4),15))

 

[T. Valko]

Thanks for getting back right away. That works great. Some of the cells in this example are blank and some contain only text.
<TABLE style="WIDTH: 67px; HEIGHT: 172px" cellSpacing=0 cellPadding=0 border=0><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">red 12</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">blue

</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">red 2

</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">14

</TD></TR></TBODY></TABLE>A5=SUM(--MID(A1:A4,FIND(" ",A1:A4),15))

Try this...

Book1
	A
1	red 12
2	blue
3	red 2
4	_
5	20
6	_
7	34
Sheet1

This array formula** entered in A7:

=SUM(IF(ISNUMBER(FIND(" ",A1:A5)),--MID(A1:A5,FIND(" ",A1:A5),15),A1:A5))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[ragweed]

Absolutely perfect. Thank you so much for your help. I will study up on array formulas so I can better understand what is going on with this formula.

ragweed

 

[T. Valko]

Absolutely perfect. Thank you so much for your help.

You're welcome. Thanks for the feedback!

I will study up on array formulas so I can better understand what is going on with this formula.

See if this helps:

http://www.cpearson.com/Excel/ArrayFormulas.aspx

 

[alireds]

Dear All,

i have query, please help out.

multiple numbers and text are in multi cell, is there any formula which can find cells that begin with "H" and then numbers and sum all numbers?

For example:

thanks a lot

 

[konew1]

Here is one way

Excel Workbook
	I
1	c45cd
2	h70
3	c45cd
4	h65
5
6	135
Sheet8

 

[alireds]

thanks a lot
you're amazing

sorry for asking again

your code is working but in my data, there is some cell with text like "h60-"
and if there is cells like it, the code have error and without these cells code working very well
is there any chance to handle this type cells like formula you wrote for "h60" without "-"

thanks a lot again

 

[konew1]

is the "-" always at the end of the string?