Sum delimited values in cell

[Andy Brown]

If I have "0.120;0.140;0.200" in a cell, is it possible to get the sum of
the values with a formula (NB: not code)?

=INDIRECT("="&SUBSTITUTE(A1,";","+")) just equals #REF!

TIA,
Andy

 

[Naz]

Hi Andy,

Unfotunately it is not possible to do what you want without code. Although
it seems like its possible, Excel won't evalute that formula to read a new
formula. Your options would be;

1) Use just the subsitute part of the formula and then maually put in the "=".
2) Use the find and replace to remove the "," and then again manually put
the "=" in. The advantage is u save a column.
3) Use the text to columns tool, to split the contents of the cells, then
put a sum formula in, though this is only practical if you don't have a large
number of delimited numbers.

If you do decide that you want to use code, paste this into a new module.
Then select the cells and run the macro.

Sub ChangeToFormula()

Dim Rng As Range

For Each Rng In Selection.Cells
Rng.Formula = WorksheetFunction.Substitute(Rng, ",", "+")
Rng.Formula = "=" & Rng.Text
Next Rng

End Sub

Hope that helps

______________________
Naz
London

 

[Niek Otten]

Hi Andy,

If it's always 3 5-digit numbers:

=SUM(LEFT(A1,5),MID(A1,7,5),RIGHT(A1,5))

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Max]

One way

With A1 containing: "0.120;0.140;0.200"

This seems to work in say, B1:

=LEFT(A1,SEARCH(";",A1)-1)+MID(A1,SEARCH(";",A1)+1,SEARCH(";",A1,SEARCH(";",
A1)+1)-SEARCH(";",A1)-1)+MID(A1,SEARCH(";",A1,SEARCH(";",A1)+1)+1,99)

 

[Bob Phillips]

Andy,

There must be a worksheet function way of doing this, but it's simple with
a UDF

Function CountNum(rng As Range, Optional delimiter As String = ",")
Dim iPos As Long
Dim istart As Long
Dim tmp

If rng.Cells.Count > 1 Then
CountNum = CVErr(xlErrRef)
Exit Function
End If
istart = 1
For iPos = 1 To Len(rng.Value)
If Mid(rng.Value, iPos, 1) = delimiter Then
tmp = tmp + CDbl(Mid(rng.Value, istart, iPos - istart))
istart = iPos + 1
End If
Next iPos
If Right(rng.Value, 1) <> delimiter Then
tmp = tmp + CDbl(Mid(rng.Value, istart, iPos - istart))
End If
CountNum = tmp
End Function

Call it like so

=countnum(AA1,";")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Got it. A nice simple formula

=SUMPRODUCT(--MID(A1,FIND("~",SUBSTITUTE(";"&A1&";",";","~",ROW(INDIRECT("1:
"&LEN(A1)-LEN(SUBSTITUTE(A1,";",""))+1)))),FIND("~",SUBSTITUTE(";"&A1&";",";
","~",ROW(INDIRECT("2:"&LEN(A1)-LEN(SUBSTITUTE(A1,";",""))+2))))-FIND("~",SU
BSTITUTE(";"&A1&";",";","~",ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,";",
""))+1))))-2))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Andy Brown]

I was specifically after a formula. Sorry if I didn't make that clear,
thanks for your suggestions.

Rgds,
Andy

 

[Andy Brown]

Niek ; sorry I didn't make clear that it could be one value, three values,
or thirty-three values. Thanks anyways.

Rgds,
Andy

 

[Andy Brown]

Max, same problem as with Niek's reply. Apols again.

Rgds,
Andy

 

[Andy Brown]

Bob, the UDF works fine. The megaformula works after a little coaxing.
Thanks very much.

Rgds,
Andy

 

[Bob Phillips]

Andy,

could you share the coaxing with us

Bob

 

[Naz]

Impressive...I think it gives rise to the quote "give me a problem and I will
smash it into the ground"

 

[Andy Brown]

Just the wrapping, nothing a little ALT+0010 trimming couldn't cure. I
certainly didn't mean to give the impression I could ever begin to
understand how to tweak it in terms of functionality.

;-) & thanks again,

Andy

 

[Bob Phillips]

Thanks Andy,

The reason I asked was because as coded it was a bit specific, and it
doesn't work for a string of say 1;2;3;4 or 11;21;31;41, and I was hoping
that your actual data had forced you to take a look and you had improved
upon it.

As you hadn't, I took another look. I also found that this 0.120;0.140;0.255
didn't work, so that gave me a clue, and I found I was truncating the
extracted number strings, so the solution was simple - change the length by
1.

This is the corrected version, which will again get the wrap-around

=SUMPRODUCT(--MID(AA1,FIND("~",SUBSTITUTE(";"&AA1&";",";","~",ROW(INDIRECT("
1:"&LEN(AA1)-LEN(SUBSTITUTE(AA1,";",""))+1)))),FIND("~",SUBSTITUTE(";"&AA1&"
;",";","~",ROW(INDIRECT("2:"&LEN(AA1)-LEN(SUBSTITUTE(AA1,";",""))+2))))-FIND
("~",SUBSTITUTE(";"&AA1&";",";","~",ROW(INDIRECT("1:"&LEN(AA1)-LEN(SUBSTITUT
E(AA1,";",""))+1))))-1))

I would actually put the delimiter value in to another cell, say B1, and use
a cell reference in the formula rather than the delimiter, to make it easier
to change

=SUMPRODUCT(--MID(AA1,FIND("~",SUBSTITUTE(B1&AA1&B1,B1,"~",ROW(INDIRECT("1:"
&LEN(AA1)-LEN(SUBSTITUTE(AA1,B1,""))+1)))),FIND("~",SUBSTITUTE(B1&AA1&B1,B1,
"~",ROW(INDIRECT("2:"&LEN(AA1)-LEN(SUBSTITUTE(AA1,B1,""))+2))))-FIND("~",SUB
STITUTE(B1&AA1&B1,B1,"~",ROW(INDIRECT("1:"&LEN(AA1)-LEN(SUBSTITUTE(AA1,B1,""
))+1))))-1))

BTW, when I copy a long formula like this from an NG posting, I just paste
it into the formula bar, and goto the end of each line and just do a one
character delete to sort it.

Regards

Bob

 

[Max]

And I can see that you've used up so many cells in row1 in your crafting
that the megaformula now refers to AA1 instead of A1 <bg>

An awesome formula, Bob !

 

[Bob Phillips]

Thanks Max.

I originally developed it in AA1, and changed it to A1 in my original reply
, but forgot the next time. Notice I was still thinking A1 if not using it
when I suggested B1 for the delimiter text <ebg>

This is one I have wanted to develop for some time, it took a real
requirement to galvanise me.

Regards

Bob

 

[Max]

.. Notice I was still thinking A1 if not using it
when I suggested B1 for the delimiter text <ebg>

LOL ! Can see that you're very fond of "A1"

This is one I have wanted to develop for some time,
it took a real requirement to galvanise me.

And we're all the richer for it ..
Thanks to Andy for the galvanizing impetus !