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

 

[DonkeyOte]

A single cell function would be quite complex... you could use a basic UDF to do the same

Function SumNums(rngS As Range, Optional strDelim As String = " ") As Double
    Dim vNums As Variant, lngNum As Long
    vNums = Split(rngS, strDelim)
    For lngNum = LBound(vNums) To UBound(vNums) Step 1
        SumNums = SumNums + Val(vNums(lngNum))
    Next lngNum
End Function

Below we use space as delimiter as this precedes numbers in the strings

Excel Workbook
	A	B
1	red 12, yellow 10, green 5	27
2	red 3, blue 9	12
3	red	0
4	3	3
Sheet5

 

[DonkeyOte]

Not clear which version of XL you're using but if you happen to be using XL2007 or above you could use:

=SUM(IFERROR((MID(SUBSTITUTE(SUBSTITUTE(A1,",","")," ",REPT(" ",LEN(A1))),1+LEN(A1)*(ROW(A$1:INDEX(A:A,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+(A1<>"")))-1),LEN(A1)))+0,0))
confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)

 

[skgurmeet]

A single cell function would be quite complex... you could use a basic UDF to do the same

Function SumNums(rngS As Range, Optional strDelim As String = " ") As Double
    Dim vNums As Variant, lngNum As Long
    vNums = Split(rngS, strDelim)
    For lngNum = LBound(vNums) To UBound(vNums) Step 1
        SumNums = SumNums + Val(vNums(lngNum))
    Next lngNum
End Function

Below we use space as delimiter as this precedes numbers in the strings

Excel Workbook
	A	B
1	red 12, yellow 10, green 5	27
2	red 3, blue 9	12
3	red	0
4	3	3
Sheet5

Thank you a lot brother... this is exactly what i was looking for... thanks..

 

[siddchip]

You're awesome! Perfect! That's exactly what I was looking for too.

Can it include a % too. As in,

Example:

A B C
1 Value Tax Total Amount
2 100 5% VAT, 2% CST 107

I want to directly add the total of taxes in the column B and want C2 to reflect it.

Possible? I would deeply grateful to you.

 

[ragweed]

I am new at posting and also VBA. I have used the Function=Sumnums and it works perfectly. I am trying to add the numbers in a range of cells that contain text and numbers but Function=Sumnums will only work on one cell. When a range is specified it returns #value!

Thanks for any help.

 

[T. Valko]

I am new at posting and also VBA. I have used the Function=Sumnums and it works perfectly. I am trying to add the numbers in a range of cells that contain text and numbers but Function=Sumnums will only work on one cell. When a range is specified it returns #value!

Thanks for any help.

Post some sample data and tell us what result you expect.

 

[Marcol]

Not clear which version of XL you're using but if you happen to be using XL2007 or above you could use:

=SUM(IFERROR((MID(SUBSTITUTE(SUBSTITUTE(A1,",","")," ",REPT(" ",LEN(A1))),1+LEN(A1)*(ROW(A$1:INDEX(A:A,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+(A1<>"")))-1),LEN(A1)))+0,0))
confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)

@ Don
I couldn't get 2003 to accept the conversion of your formula.
However this seems to work.
I can't get my head around how to replace the fixed arrays {1,2,3,4,5,6,7,8,9,10} to get it to work with any string length. any ideas?

Excel Workbook
	A	B
1	String	Result
2	red 12, yellow 10, green 5	27
3	red 3, blue 9	12
4	yellow 21, blue 3	24
Sheet1

 

[Marcol]

Got it, but is it possible to find a non-array entered solution?
(Allowing for the fact that SUMPRODUCT() is an array function.)

=SUMPRODUCT(IF(ISERROR(SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1),0,SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2," ",REPT(" ",100)),COLUMN($1:$1)*100-99,100)),",","")*1))

Confirm with Ctrl+Shift+Enter not just Enter

 

[ragweed]

Post some sample data and tell us what result you expect.

<table style="width: 273px; height: 113px;" border="1" cellpadding="0" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><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)