newbie need help:sum number inside string

cuongvt · #1 (**permalink**) 03-02-05, 23:10

Could anyone help me on this problem please, I'm new to formula and really don't know what to do now:
On cell:
A1 have a string: 2c3
b1 have a string: 3c5
c1 have a string: 0.5c3
d1 have a string: 5c2.5
e1 have a string: 5c
f1 have a string: 0.5c

All I want is sum the number in right most of the above strings.
But I don't know the axact formula to do it. (In this case the result must be
13.5)
Please help me, I need your help!
many thanks

shades · #2 (**permalink**) 03-02-05, 23:33

You have to deal with a couple of issues: these are not "numbers" according to Excel (double negative signs to coerce the result to be a number), and the sum has to take into account those situations in which the number to the right of "c" is absent (the condition in the IF formula).

I assume that the letter will always be "c" in the string. If so, then in A2, put this formula:

=IF(LEN(A1)-LEN(LEFT(A1,FIND("c",A1)))=0,0,--(RIGHT(A1,LEN(A1)-LEN(LEFT(A1,FIND("c",A1))))))

Then copy it to the right so that B2:F2 are filled with the corresponding formulas.

Then in G2 put

=SUM(A2:F2)

cuongvt · #3 (**permalink**) 03-03-05, 01:30

thanks. it works.
Sorry to bother you, but could you give me a sum formula so that I can put it in f2 cell and it can sum on whole line from a1 to f1?
many thanks.

cuongvt · #4 (**permalink**) 03-03-05, 05:36

I'm sorry. Not F2, I want the sum formula in g1.
many thanks.
Please help me. I need your help.

DavidCoutts · #5 (**permalink**) 03-03-05, 07:51

I have a feeling you will have to put together a udf for this
as not all the formulas required can handle array values

try something like this

Code:

Function SumAfterChar(rngSum As Range, Chr As String) As Double
    Dim cl As Range
    For Each cl In rngSum
        If InStr(1, cl, Chr) > 0 And InStr(1, cl, Chr) < Len(cl) Then
            SumAfterChar = SumAfterChar + CDbl(Right(cl, Len(cl) - InStr(1, cl, Chr)))
        End If
    Next cl
End Function

but i may be wrong

Dave

cuongvt · #6 (**permalink**) 03-03-05, 22:05

Could you help me again:
On g1 cell, I put =SumAfterChar(A1:f1,"c"). But Excel say: #NAME?
many thanks

DavidCoutts · #7 (**permalink**) 03-04-05, 01:53

did you copy the code into a module in your workbook,
the function will only work if you put the code into aa module

try searching for user defined function on google for more explanantion

Internet Services

Web Development

Digital Marketing

Consumer Tech