Split numbers and split words

How do I split numbers!

I have two problems/challenges!

Part I...
I got the answer 1987, and now I want Excel to take the numbers out and display...
1 in one box then i set + in the next, then 9 in the 3ed. box, next box +, then 8, then +, then 7 in the last so that i can have Excel make a SUM of it all to 25.

How do I split 1987 and put the numbers in different boxes?

Part II...
I want to make A=1 B=2... all the way up to 9, then start over again with J=1 K=2... up to 9 again and then over again.

So that if I write my name it comes out as a value of 14 (Odd = O=6 D=4 D=4 =14)

(AJSØ=1 BKTÅ=2 CLU=3 DMV=4 ENW=5 FOX=6 GPY=7 HQZ=8 IRÆ=9, It's the Norwegian alphabet, that's why there are some extra letters)

So how do I set up my Excel so that is ANY name is typed in I can get it out into a number from the values assign?

Odd K.
Thank you very much for any help!

Sorry about the title :D

you don't really need to split it if you want the sum you can use
=SUM(VALUE(MID(B1,ROW($A$1:OFFSET($A$1,LEN(B1)-1,0)),1))) array entered
with ctrl shift and enter.
if you must split it out
i am going to assume the number can be
any length then its just best to use mid as it saves time
=--MID($B$1,COLUMN(),1) dragged across from col a will split whats in b1 which you can sum
you can use the same technique to extract the letters from a name
but leave off the --
=MID($B$1,COLUMN(),1)
then look up each of the letters in a table you would set up somewhere

Oddemann, Welcome to the Board.

Per forum rules please re-title your thread to something more appropriate.

Re: 1st question
You need not split the values

B1: =SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},"")),{1,2,3,4,5,6,7,8,9})
where A1 holds 1987

Re: 2nd question

The simplest thing will be to have a column holding letters of the alphabet and the adjacent column holding the associated value then adopt a similar approach to the above

D1: =SUMPRODUCT(LEN(C1)-LEN(SUBSTITUTE(C1,F1:F26,"")),G1:G26)
where C1 holds name and F1:F26 letters of the alphabet and G1:G26 the associated values of each letter... alter ranges accordingly.

Hi,

Part 1 - to extract the numbers, use the excel MID function.

To total all the digits in a cell, =SUMPRODUCT((MID(SUBSTITUTE(A1,".",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,".","")))),1)+0))

You could use a UDF such as the following to convert the letters to numbers

Please Login or Register  to view this content.

Oddemann

Please take a couple of minutes and read ALL the Forum Rules then edit your thread title by following the instructions in the rules (Rule 1) so that it better discribes your problem/request

What should I give it as a title the question?

Now its "partly" fix, sorry for the bad title! :D

Tried, but got nowhere fast, so i give you a "picture" of how it looks so i can get a grip of the formula and how to insert it!

http://www.flickr.com/photos/31335723@N06/3512884128/

Thank you all for very fast response and some really good answers!

Odd K.

to do it as in your example
since thats the layout you require

Upload a sample file rather than an image...

I suspect your issues relate to regional settings - ie our offerings are UK based using , as delimiter as opposed to ; as may be the case for you in Norway.

[deleted....]

There was no mistake it is simply a case of translation - formula delimiters in XL will vary pending regional settings on the client machine.

I now want to test a box with IF

So IF it has a 3 number, like 109, then its is 9. IF its not 3 numbers then put out 0

109 So if it is 3 numbers then i get 1 + 0 + 9 = 10 < No problem with the formula, since I have 3 numbers to add!

58 So if it is 2 numbers then i get 5 + 8 + " " = 13 < No i HAVE a problem with the formula, since I have only 2 numbers to add, the lack of a the 3 digit stops the formula!

=IF(N13;--MID(N13;3;1);0)

This formula puts out 9 if there is 3 digits but if its only 2 i get a error in the formula.

Help, what am i going wrong?

Odd K.
Continue testing and playing here!

Perhaps

=9*AND(N13>=100;N13<=999)

Oddeman,

You should start new threads with new questions rather than extended a thread with multiple unrelated questions.

Thanks! Worked great! =9*AND(N13>=100;N13<=999)

But, how dos it work? Dont understand it, it gives the right answers, but I also like to understand it!

Thank you very much!

Odd K.
Testing new formula to learn!

PS! Should I do this NOW or next time?

Originally Posted by Oddemann

PS! Should I do this NOW or next time?

Next time...

DonkeyOte can continue to assist here if he desires.

Whilst trying to avoid incurring NBVC's wrath...

How does it work ?

Well the AND test will return a Boolean of either TRUE or FALSE
(TRUE will only be returned if the value in N13 is between 100 and 999)

TRUE when coerced to Integer (via multiplication, addition etc) equates to 1 whereas FALSE coerces to 0, thus:

=9*TRUE -> 9 * 1 -> 9

whereas

=9*FALSE -> 9 * 0 -> 0

I don't really see how the file relates to the last question re: 3 digits ... it's also not clear as to whether B5 should be 0 or 1 ... ie B5 relates to 100's, D5 to 10's and F5 to 1's... ie 0, 1, 0 ... if so:

B5: =INT(H3/100)
D5: =INT(MOD(H3,100)/10)
F5: =MOD(H3,10)

If on the other hand B5 should be 1st digit, D5 2nd digit (if exists) and so forth and 0 if no digit exists then you could use:

B5: =LOOKUP(10,CHOOSE({1,2},0,0+MID($H3,1,1)))
D5: =LOOKUP(10,CHOOSE({1,2},0,0+MID($H3,2,1)))
F5: =LOOKUP(10,CHOOSE({1,2},0,0+MID($H3,3,1)))

I would suggest you post a clear sample with (multiple) desired results...

Are these results all correct?

Please Login or Register  to view this content.

=mod(a1 - 1, 9) + 1

A better formula, compliments of (who else?) Daddylonglegs in another thread, works around the problem described at http://support.microsoft.com/kb/119083

=A2 - FLOOR(A2 - 1, 9)

=A2 - FLOOR(A2 - 1, 9)
q. does it work?
perhaps another uploaded example would be enlightening if none of those do!

in D34 you have =H32-(INT(H32/B1)*B1)
why B1 ?
b1 is text on that sheet it says
"FN"
and i have absolutely no idea why you suddenly decided to add 1 in cell F32

you have to adjust references yourself so why did you use B1?

see sheet 2
i assume you will only ever have a max of 3 digits in each of fn mn en?

If all you're after is the single result, you could just do =SUM(A2:C2) - FLOOR(SUM(A2:C2) - 1, 9), irrespective of whether there are 1, 2 or 3 numbers in A2:C2.