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:
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
Please help me, I need your help!
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:
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
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)))