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?