• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

add the numbers in a single cell together

F

FreakyGirl

Member
Hello.

I would like to add anywhere from 1 to 13 numbers in a single cell together.

ie: 23456789

i tried using
Code: 
=IF(LEN(I11)=1,I11,IF(LEN(I11=2),SUM(LEFT(I11),MID(I11,2,1),IF(LEN(I11)=3,SUM(LEFT(I11),MID(I11,2,1),MID(I11,3,1)),IF(LEN(I11)=4,SUM(LEFT(I11),MID(I11,2,1),MID(I11,3,1),MID(I11,4,1)),IF(LEN(I11)=5,SUM(LEFT(I11),MID(I11,2,1),MID(I11,3,1),MID(I11,4,1),MID(I11,5,1)),IF(LEN(I11)=6,SUM(LEFT(I11),MID(I11,2,1),MID(I11,3,1),MID(I11,4,1),MID(I11,5,1),MID(I11,6,1)),IF(LEN(I11)=7,SUM(LEFT(I11),MID(I11,2,1),MID(I11,3,1),MID(I11,4,1),MID(I11,5,1),MID(I11,6,1),MID(I11,7,1)),IF(LEN(I11)=8,SUM(LEFT(I11),MID(I11,2,1),MID(I11,3,1),MID(I11,4,1),MID(I11,5,1),MID(I11,6,1),MID(I11,7,1),MID(I11,8,1)),SUM(LEFT(I11),MID(I11,2,1),MID(I11,3,1),MID(I11,4,1),MID(I11,5,1),MID(I11,6,1),MID(I11,7,1),MID(I11,8,1),MID(I11,9,1)))))))))))

but thats only allowing me 9 numbers & the result isnt right.

im also not sure how to handle the '10' because it needs to be considered as 10 not as 1 & then 0 as another digit.

additionally, the last 4 numbers (when used) woudl be 1 1 1 1 so if all 13 digits were present in the cell it would say 2345678910111 & add up to 58

they are listed in the cell I11 in number order up to the last 4 digits like below.
2 3 6 7 9 1 1
3 4 5 6 8 9
2 8 10 1

is there a better way?

Thanks,
FreakyGirl
 
Are they separated by spaces?
If not then how do you distinguish 10 from 1 and 0?

With 13 helper columns to delimit space delimited string you can verify the results. Example file is attached herewith.
 

Attachments

  • chandoo_38081.xlsx
    10.9 KB · Views: 8
BTW, with string: 2 3 4 5 6 7 8 9 10 1 1 1
SUM is: 57 and not 58

With Lori's formula (particular keyword is of importance) largest allowed digit is 10.
 
Hello

Thanks for the great solutions!

i apologize, it does add up to 57 but i left off a 1 on the end by accident so it should have been 23456789101111.

yes, that would be a problem with ali's & lori's solution. they both work but only for single digits & the 10 would be present in some cells.

the formula provided by shrivallabha works perfectly with the 10.

however, now that i have seen how it could be done with helper columns & substitute i would like to do what my original plan was.


those numbers represent the 13 cards in any suit.

because i didnt know how to make the letters J,Q,K,A change to a digit i was just going to enter them as a 1 as that is their value.

3 5 7 9 J Q would be entered as 3 5 7 9 1 1

but later when im getting other info off this data i wont know the actual face card that was there. was it the J & Q or the J & K?

so if i put the true data in A2 .. 2 3 6 7 9 J A then in B2 have the following formula
Code: 
=IF(RIGHT(A2)="A",SUBSTITUTE(A2,RIGHT(A2),1,1))

it does change the "A" to a 1 which is then broken down in the helper columns perfectly.

how can i expand that formula to cover the possibility of any combo of J Q K A?

thanks for your time & effort,
FreakyGirl
 
@FreakyGirl hmm, never hide details from doctors, lawyers and Excel forum members :D

Now that you have told us what you are up to,

let's say a cell (B4) has the text 1023456789jqka
You can get the total value of that with below array formula

Code: 
=SUM(IFERROR(VALUE(MID(B4,ROW(OFFSET($A$1,,,LEN(B4),1)),1)),10),(LEN(B4)-LEN(SUBSTITUTE(B4,"1","")))*9)

This just assumes there is no card as 1 but there is a 10. Any non-numeric codes are turned to 10
Do not enter any spaces. If you must, then replace B4's with substitute(B4," ","") and it should work ok.
 
Hi ,

Even now , all of the information has not been revealed.

What exactly will different cells contain ?

Will they contain a list of all the 13 cards or some subset of this ?

Can you not just use a lookup table , where all the cards are listed along with their values ? That way , you can enter the actual card designations in the cells , and then use VLOOKUP to lookup their values from the lookup table.

Narayan
 
Adding the ace and face cards could simplify it a little:

=SUM(ISNUMBER(SEARCH({2,3,4,5,6,7,8,9,10,"J","Q","K","A"},I11))*{2,3,4,5,6,7,8,9,10,1,1,1,1})

(Also maybe an idea to use "T" for 10 so each card is represented by a single character)
 
hello

ROFL @ r2c2 i see this now *hangs head in shame*

ty for the suggestions & working formulas! they both worked perfectly!!

i love this site! so many always willing to help :)

have a GREAT day to all the AWESOME members!

FreakyGirl
 
You must log in or register to reply here.
Back
Top