Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Posts
    133

    Wink Unanswered: newbie need help:sum number inside string

    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

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    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)
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    May 2004
    Posts
    133
    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.

  4. #4
    Join Date
    May 2004
    Posts
    133
    I'm sorry. Not F2, I want the sum formula in g1.
    many thanks.
    Please help me. I need your help.

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

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

  7. #7
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •