Sum delimited values in cell
 

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

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

"Andy Brown" wrote:

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

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

"Andy Brown" wrote in message
...
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

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)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
Andy Brown wrote in message
...
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

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)


"Andy Brown" wrote in message
...
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

Got it. A nice simple formula :-)

=SUMPRODUCT(--MID(A1,FIND("~",SUBSTITUTE(";"&A1&";",";","~",ROW( INDIRECT("1:
"&LEN(A1)-LEN(SUBSTITUTE(A1,";",""))+1)))),FIND("~",SUBSTITU TE(";"&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)


"Bob Phillips" wrote in message
...
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)


"Andy Brown" wrote in message
...
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

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

Rgds,
Andy


"Naz" wrote in message
...

Unfotunately it is not possible to do what you want without code.
...
If you do decide that you want to use code, ...

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

Rgds,
Andy


"Niek Otten" wrote in message
...
Hi Andy,

If it's always 3 5-digit numbers:

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

Rgds,
Andy


"Max" wrote in message
...
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, the UDF works fine. The megaformula works after a little coaxing.
Thanks very much.

Rgds,
Andy



"Bob Phillips" wrote in message
...
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)


"Andy Brown" wrote in message
...
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

Andy,

could you share the coaxing with us :-)

Bob


"Andy Brown" wrote in message
...
Bob, the UDF works fine. The megaformula works after a little coaxing.
Thanks very much.

Rgds,
Andy



"Bob Phillips" wrote in message
...
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)


"Andy Brown" wrote in message
...
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

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

"Bob Phillips" wrote:

Got it. A nice simple formula :-)

=SUMPRODUCT(--MID(A1,FIND("~",SUBSTITUTE(";"&A1&";",";","~",ROW( INDIRECT("1:
"&LEN(A1)-LEN(SUBSTITUTE(A1,";",""))+1)))),FIND("~",SUBSTITU TE(";"&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)


"Bob Phillips" wrote in message
...
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)


"Andy Brown" wrote in message
...
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

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" wrote in message
...
Andy,

could you share the coaxing with us :-)

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&";",";","~",RO W(INDIRECT("
1:"&LEN(AA1)-LEN(SUBSTITUTE(AA1,";",""))+1)))),FIND("~",SUBSTIT UTE(";"&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(I NDIRECT("1:"
&LEN(AA1)-LEN(SUBSTITUTE(AA1,B1,""))+1)))),FIND("~",SUBSTITU TE(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

"Andy Brown" wrote in message
...
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" wrote in message
...
Andy,

could you share the coaxing with us :-)

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 !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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" wrote in message
...
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 !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

"Bob Phillips" wrote
....
.. 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"
Like many others, I would figure <bg

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 !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----