Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
changing numbers in a text string in a new cell | Excel Discussion (Misc queries) | |||
How can I Import picture contents into Excell cell array numbers? | Excel Worksheet Functions | |||
How can I Import picture contents into Excell cell array numbers? | Excel Worksheet Functions | |||
last number array from string | Excel Worksheet Functions | |||
How do you extract numbers from a string of chacters in a cell (E. | Excel Worksheet Functions |