Lottery Filter#1, SUM in Single CELL with "-"

Try this...

Data Range

	G	H
6	22-33-34-35-36	160
7	08-28-29-30-35	130
8	01-18-23-33-35	110

This array formula** entered in H6 and copied down:

=SUM(IF(MID("-"&G6,COLUMN(1:1),1)="-",--(0&MID(G6,COLUMN(1:1),FIND("-",G6&"-",COLUMN(1:1))-COLUMN(1:1)))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

=VALUE(LEFT(G6,2)+MID(G6,4,2)+MID(G6,7,2)+MID(G6,10,2)+RIGHT(G6,2))
Try this one

You're welcome. Thanks for the feedback!

If the numbers will ALWAYS be 2 digit numbers you'd be better off using something simpler like that in post #3.

Try this tweak to popipipo's formula.

=LEFT(G6,2)+MID(G6,4,2)+MID(G6,7,2)+MID(G6,10,2)+RIGHT(G6,2)

Worked OK for me.

Can you post the example where it returns that error?

awesome formula, Tony. i didn't think it was possible to sum those values, except to extract parts by parts painfully using LEFT, RIGHT & MID. for this case of course, it will be slightly easier. but Tony's formula comes in really handy with longer combinations or with a non fixed structure of 2 digits each. totally going into my compilation & here's my attempt in explaining it to help the OP understand too:

="-"&G6
1 character after the "-" is where the numbers to add up are. see first dash, add 33. see second dash, add 34, & so on. but the first number is missed out because there is no dash in front of 22. hence, the formula first adds a "-" to G6 so that all are equal.

=COLUMN(1:1)
COLUMN(A1) returns 1. that's the column number of A1. COLUMN B1 returns 2. if you use COLUMN(A1:C1), it returns all the column numbers in that range like this:
{1,2,3}
COLUMN(1:1) will therefore return 1 to 16,384 if you are using xlsx extension (up to Column XFD)

=MID("-"&G6,COLUMN(1:1),1)
this part is to break up all the characters into parts of 1 each. so since we combined "-" with G6, we are looking at this text below:
"-22-33-34-35-36"
and in here, it's saying, from that text above, start from the 1st character (using COLUMN), and 1 character long. but since COLUMN was used, it continues to extract form the 2nd character, 3rd character & so on until 16,384 characters. the results are something like:{"-","2","2","-","3","3","-","3","4","-","3","5","-","3","6","","","",..........}

MID("-"&G6,COLUMN(1:1),1)="-"
this is to check if the extraction is a "-". so only if it is a "-", the formula will do a MID formula extraction in the next part. it will become TRUEs & FALSEs:
{TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,...............}

=FIND("-",G6&"-",COLUMN(1:1))-COLUMN(1:1)
FIND is used to find a dash in this text below using G6 combined with a dash behind.
"22-33-34-35-36-"
the start_num of FIND is also used using COLUMN(1:1). 1 is simply to start finding "-" in the 1st character. that will return 3. if i were to start finding in the 4th character, the next "-" is in character 6. at the end the formula, it minus 1 character for the 1st extraction using COLUMN(1:1). so 3-1 is 2. 6 would be deducting 4, because we're doing the when we start finding in the 4th character, COLUMN would become 4. so it's always 2 characters for this case. it will roughly look like:
{2,1,0,2,1,0,2,1,0,2,1,0,2,1,0,#VALUE!,#VALUE!,......}
VALUE error is due to "-" not being found any more after 15 characters.

=MID(G6,COLUMN(1:1),FIND("-",G6&"-",COLUMN(1:1))-COLUMN(1:1))
the first extraction would be from the text in G6, start from the 1st character (using COLUMN again). and the number of characters 2 since FIND returns that. that gives us 22 (the 1st number we want to add).
the 4th extraction would again be from text in G6, starting from 4th character (since COLUMN will give 4) & a length of 2 characters again, obtained by FIND. that gives us 33 (the 2nd number we want to add).
in summary:
{"22","2","","33","3","","34","4","","35","5","","36","6","",#VALUE!,#VALUE!,#VALUE!}
2 things here; these numbers are actually texts with the double quotes. hence we can't add them up. also, notice that we don't want some of those numbers, namely the 2nd, 3rd, 5th, 6th, etc numbers.

=--(0&MID(G6,COLUMN(1:1),FIND("-",G6&"-",COLUMN(1:1))-COLUMN(1:1)))
adding the double negatives will make all the texts inside the brackets numbers, if they are indeed are numbers. the 0& is to add a 0 in front of those numbers. not sure why is that necessary. maybe Tony can explain this

=SUM(IF(MID("-"&G6,COLUMN(1:1),1)="-",--(0&MID(G6,COLUMN(1:1),FIND("-",G6&"-",COLUMN(1:1))-COLUMN(1:1)))))
using the logical test we did in the inital stage, we can extract those numbers ONLY IF the single character we extracted are the "-".

OK, the problem is that the number strings in column G have 9 trailing space characters. Like this:

22-33-34-35-36[sp|sp|sp|sp|sp|sp|sp|sp|sp]

So, you can use this more efficient formula in H6:

=IF(G6="","",LEFT(G6,2)+MID(G6,4,2)+MID(G6,7,2)+MID(G6,10,2)+RIGHT(TRIM(G6),2))

And this formula in I6:

=IF(H6="","",LEFT(H6)+MID(H6,2,1)+IF(LEN(H6)=3,RIGHT(H6),0))

Copy down as needed.

You'll notice that there is no longer the "delay" when entering data.

If the number strings in column G come from an external source you can "clean" those trailing spaces by using this macro:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Improvements...

Entered in H6:

=IF(G6="","",SUM(--MID(G6,{1,4,7,10,13},2)))

Entered in I6:

=IF(H6="","",SUM(--MID(H6&0,{1,2,3},1)))

One more:

In Name Manager, create a new name, say Lottery_Sum, and type this in the Refers to: box:

=-EVALUATE("-"&Sheet1!$G6)

Exit Name Manager. Then, in H6, enter:

=Lottery_Sum

Copy down as required.

Regards