# Thread: newbie need help:sum number inside string

1. Registered User
Join Date
May 2004
Posts
133

## 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)
many thanks

2. Registered User
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)

3. Registered User
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. Registered User
Join Date
May 2004
Posts
133
I'm sorry. Not F2, I want the sum formula in g1.
many thanks.

5. Registered User
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. Registered User
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. Registered User
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
•