sum numbers in cell string array
 

I need to summ the numeris value of a cell string. The input of the cell will
be alphanumeric and semicolon delimited.

Example:
A1 = GBR 4; FRA 5; USA 11
result B1 = 20 (4+5+11)

I have come up with a few working examples but I would like to do it in less
steps and cells.

in b1 now I have
=VALUE(IF(ISERROR(RIGHT(LEFT(A1&";",(FIND(CHAR(1), SUBSTITUTE(A1&";",";",CHAR(1),1))-1)),1)),0,RIGHT(LEFT(A1&";",(FIND(CHAR(1),SUBSTITU TE(A1&";",";",CHAR(1),1))-1)),1)))

I added ";" because i am using that for my reference and going 2 digits
left. numbers will not be larger than 99 and the text will be 3 digits with
space. the number of entries are unknown.

so in c1 I added this formula

=VALUE(IF(ISERROR(RIGHT(LEFT(A1&";",(FIND(CHAR(1), SUBSTITUTE(A1&";",";",CHAR(1),2))-1)),2)),0,RIGHT(LEFT(A1&";",(FIND(CHAR(1),SUBSTITU TE(A1&";",";",CHAR(1),2))-1)),2)))

I continue this through the columns 15 more times. Then I sum the results in
another column.

I when I tried to put all the formulas in the SUM() in a cell received an
error nesting exceeded. I have to do the if because for the iserror when
there is no number.
A1 may have 3 entries and B1 may have 6.

Is there a way, formula, or vb that can be used to sum the numbers os a cell
string array?

sum numbers in cell string array
 

Try the User Defined Function
Function splitSum(rng As Range) As Integer
x = Split(rng, ";")
Sum = 0
For i = 0 To UBound(x)
Sum = Sum + Right(x(i), 2)
Next i
splitSum = Sum
End Function

with GBR 4; FRA 5; USA 11; USD 12; GBR 54 in A1
=splitSum(A1)
will give you 86

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"kookie" wrote:

I need to summ the numeris value of a cell string. The input of the cell will
be alphanumeric and semicolon delimited.

Example:
A1 = GBR 4; FRA 5; USA 11
result B1 = 20 (4+5+11)

I have come up with a few working examples but I would like to do it in less
steps and cells.

in b1 now I have
=VALUE(IF(ISERROR(RIGHT(LEFT(A1&";",(FIND(CHAR(1), SUBSTITUTE(A1&";",";",CHAR(1),1))-1)),1)),0,RIGHT(LEFT(A1&";",(FIND(CHAR(1),SUBSTITU TE(A1&";",";",CHAR(1),1))-1)),1)))

I added ";" because i am using that for my reference and going 2 digits
left. numbers will not be larger than 99 and the text will be 3 digits with
space. the number of entries are unknown.

so in c1 I added this formula

=VALUE(IF(ISERROR(RIGHT(LEFT(A1&";",(FIND(CHAR(1), SUBSTITUTE(A1&";",";",CHAR(1),2))-1)),2)),0,RIGHT(LEFT(A1&";",(FIND(CHAR(1),SUBSTITU TE(A1&";",";",CHAR(1),2))-1)),2)))

I continue this through the columns 15 more times. Then I sum the results in
another column.

I when I tried to put all the formulas in the SUM() in a cell received an
error nesting exceeded. I have to do the if because for the iserror when
there is no number.
A1 may have 3 entries and B1 may have 6.

Is there a way, formula, or vb that can be used to sum the numbers os a cell
string array?

sum numbers in cell string array
 

the number of entries are unknown.

I wouldn't even mess around trying to get a formula to work on this.

I would:

EditReplace
Find what: ;
Replace with: nothing, leave this empty
Replace All

Make sure lots of columns to the right are empty...

DataText to Columns
DelimitedSpaceFinish

Then:

=SUM(A1:J1)

Someone might be able to come up with a UDF that'll work.


--
Biff
Microsoft Excel MVP


"kookie" wrote in message
...
I need to summ the numeris value of a cell string. The input of the cell
will
be alphanumeric and semicolon delimited.

Example:
A1 = GBR 4; FRA 5; USA 11
result B1 = 20 (4+5+11)

I have come up with a few working examples but I would like to do it in
less
steps and cells.

in b1 now I have
=VALUE(IF(ISERROR(RIGHT(LEFT(A1&";",(FIND(CHAR(1), SUBSTITUTE(A1&";",";",CHAR(1),1))-1)),1)),0,RIGHT(LEFT(A1&";",(FIND(CHAR(1),SUBSTITU TE(A1&";",";",CHAR(1),1))-1)),1)))

I added ";" because i am using that for my reference and going 2 digits
left. numbers will not be larger than 99 and the text will be 3 digits
with
space. the number of entries are unknown.

so in c1 I added this formula

=VALUE(IF(ISERROR(RIGHT(LEFT(A1&";",(FIND(CHAR(1), SUBSTITUTE(A1&";",";",CHAR(1),2))-1)),2)),0,RIGHT(LEFT(A1&";",(FIND(CHAR(1),SUBSTITU TE(A1&";",";",CHAR(1),2))-1)),2)))

I continue this through the columns 15 more times. Then I sum the results
in
another column.

I when I tried to put all the formulas in the SUM() in a cell received an
error nesting exceeded. I have to do the if because for the iserror when
there is no number.
A1 may have 3 entries and B1 may have 6.

Is there a way, formula, or vb that can be used to sum the numbers os a
cell
string array?

sum numbers in cell string array
 

On Thu, 9 Apr 2009 19:39:02 -0700, kookie
wrote:

I need to summ the numeris value of a cell string. The input of the cell will
be alphanumeric and semicolon delimited.

Example:
A1 = GBR 4; FRA 5; USA 11
result B1 = 20 (4+5+11)



You could download and install Longre's morefunc.xll add-in (Google to find a
download site), then use this formula:

=EVAL(REGEX.SUBSTITUTE(A1,"\D+","+"))

This assumes your numeric values are all integers. If they might be decimals,
then we can make some minor changes.

If you cannot find a download site, we can easily implement this in VBA.
--ron

sum numbers in cell string array
 

Since there will always be a space in front of the number, I would use that
fact in order to produce this perhaps more compact User Defined Function...

Function SumIt(S As String) As Double
For Each V In Split(S)
SumIt = SumIt + Val(V)
Next
End Function

--
Rick (MVP - Excel)


"Sheeloo" just remove all As... wrote in
message ...
Try the User Defined Function
Function splitSum(rng As Range) As Integer
x = Split(rng, ";")
Sum = 0
For i = 0 To UBound(x)
Sum = Sum + Right(x(i), 2)
Next i
splitSum = Sum
End Function

with GBR 4; FRA 5; USA 11; USD 12; GBR 54 in A1
=splitSum(A1)
will give you 86

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"kookie" wrote:

I need to summ the numeris value of a cell string. The input of the cell
will
be alphanumeric and semicolon delimited.

Example:
A1 = GBR 4; FRA 5; USA 11
result B1 = 20 (4+5+11)

I have come up with a few working examples but I would like to do it in
less
steps and cells.

in b1 now I have
=VALUE(IF(ISERROR(RIGHT(LEFT(A1&";",(FIND(CHAR(1), SUBSTITUTE(A1&";",";",CHAR(1),1))-1)),1)),0,RIGHT(LEFT(A1&";",(FIND(CHAR(1),SUBSTITU TE(A1&";",";",CHAR(1),1))-1)),1)))

I added ";" because i am using that for my reference and going 2 digits
left. numbers will not be larger than 99 and the text will be 3 digits
with
space. the number of entries are unknown.

so in c1 I added this formula

=VALUE(IF(ISERROR(RIGHT(LEFT(A1&";",(FIND(CHAR(1), SUBSTITUTE(A1&";",";",CHAR(1),2))-1)),2)),0,RIGHT(LEFT(A1&";",(FIND(CHAR(1),SUBSTITU TE(A1&";",";",CHAR(1),2))-1)),2)))

I continue this through the columns 15 more times. Then I sum the results
in
another column.

I when I tried to put all the formulas in the SUM() in a cell received an
error nesting exceeded. I have to do the if because for the iserror when
there is no number.
A1 may have 3 entries and B1 may have 6.

Is there a way, formula, or vb that can be used to sum the numbers os a
cell
string array?

sum numbers in cell string array
 

I like this response, but I am not sure how to implement a user defined
function. Do I add this as code in the code view, or is there another way?

thanks

"Sheeloo" wrote:

Try the User Defined Function
Function splitSum(rng As Range) As Integer
x = Split(rng, ";")
Sum = 0
For i = 0 To UBound(x)
Sum = Sum + Right(x(i), 2)
Next i
splitSum = Sum
End Function

with GBR 4; FRA 5; USA 11; USD 12; GBR 54 in A1
=splitSum(A1)
will give you 86

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"kookie" wrote:

I need to summ the numeris value of a cell string. The input of the cell will
be alphanumeric and semicolon delimited.

Example:
A1 = GBR 4; FRA 5; USA 11
result B1 = 20 (4+5+11)

I have come up with a few working examples but I would like to do it in less
steps and cells.

in b1 now I have
=VALUE(IF(ISERROR(RIGHT(LEFT(A1&";",(FIND(CHAR(1), SUBSTITUTE(A1&";",";",CHAR(1),1))-1)),1)),0,RIGHT(LEFT(A1&";",(FIND(CHAR(1),SUBSTITU TE(A1&";",";",CHAR(1),1))-1)),1)))

I added ";" because i am using that for my reference and going 2 digits
left. numbers will not be larger than 99 and the text will be 3 digits with
space. the number of entries are unknown.

so in c1 I added this formula

=VALUE(IF(ISERROR(RIGHT(LEFT(A1&";",(FIND(CHAR(1), SUBSTITUTE(A1&";",";",CHAR(1),2))-1)),2)),0,RIGHT(LEFT(A1&";",(FIND(CHAR(1),SUBSTITU TE(A1&";",";",CHAR(1),2))-1)),2)))

I continue this through the columns 15 more times. Then I sum the results in
another column.

I when I tried to put all the formulas in the SUM() in a cell received an
error nesting exceeded. I have to do the if because for the iserror when
there is no number.
A1 may have 3 entries and B1 may have 6.

Is there a way, formula, or vb that can be used to sum the numbers os a cell
string array?

sum numbers in cell string array
 

Never mind, I did some research, implemented it, and that is awesome.

thanks so much,

"Sheeloo" wrote:

Try the User Defined Function
Function splitSum(rng As Range) As Integer
x = Split(rng, ";")
Sum = 0
For i = 0 To UBound(x)
Sum = Sum + Right(x(i), 2)
Next i
splitSum = Sum
End Function

with GBR 4; FRA 5; USA 11; USD 12; GBR 54 in A1
=splitSum(A1)
will give you 86

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"kookie" wrote:

I need to summ the numeris value of a cell string. The input of the cell will
be alphanumeric and semicolon delimited.

Example:
A1 = GBR 4; FRA 5; USA 11
result B1 = 20 (4+5+11)

I have come up with a few working examples but I would like to do it in less
steps and cells.

in b1 now I have
=VALUE(IF(ISERROR(RIGHT(LEFT(A1&";",(FIND(CHAR(1), SUBSTITUTE(A1&";",";",CHAR(1),1))-1)),1)),0,RIGHT(LEFT(A1&";",(FIND(CHAR(1),SUBSTITU TE(A1&";",";",CHAR(1),1))-1)),1)))

I added ";" because i am using that for my reference and going 2 digits
left. numbers will not be larger than 99 and the text will be 3 digits with
space. the number of entries are unknown.

so in c1 I added this formula

=VALUE(IF(ISERROR(RIGHT(LEFT(A1&";",(FIND(CHAR(1), SUBSTITUTE(A1&";",";",CHAR(1),2))-1)),2)),0,RIGHT(LEFT(A1&";",(FIND(CHAR(1),SUBSTITU TE(A1&";",";",CHAR(1),2))-1)),2)))

I continue this through the columns 15 more times. Then I sum the results in
another column.

I when I tried to put all the formulas in the SUM() in a cell received an
error nesting exceeded. I have to do the if because for the iserror when
there is no number.
A1 may have 3 entries and B1 may have 6.

Is there a way, formula, or vb that can be used to sum the numbers os a cell
string array?