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

 

[BarryHatlle]

Oh no no, i got the formula from the same thread but in different page, so i was trying to use that formula at first until i got the answer from you. just trying to work and learn different formula that's all.