Home 
Search 
Today's Posts 
#1




Convert letters into numbers. i.e. ABCD = 52
I work with DNA and would like to assign numerical values to the bases. i.e.
A = 32 etc. Ideally, I just want to past a DNA sequence into Excel, e.g. ATGCCA and then have a numerical display of the value obtained if these are summed. e.g. if A=32, AA=64, AAA=96 etc. Any way I can do this? Thanks! 
#2




Convert letters into numbers. i.e. ABCD = 52
Try this:
For a DNA sequence in Cell A1 B1: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))33) Does that help? *********** Regards, Ron "Rhapsody 1234" wrote: I work with DNA and would like to assign numerical values to the bases. i.e. A = 32 etc. Ideally, I just want to past a DNA sequence into Excel, e.g. ATGCCA and then have a numerical display of the value obtained if these are summed. e.g. if A=32, AA=64, AAA=96 etc. Any way I can do this? Thanks! 
#3




Convert letters into numbers. i.e. ABCD = 52
Very cool, Ron.

#4




Convert letters into numbers. i.e. ABCD = 52

#5




Convert letters into numbers. i.e. ABCD = 52
That's quite brilliant.
Would you mind explaining (in English) how this sumproduct is working please. I thought sumproduct needed at least a couple of arrays, but this only appears to have one. Rgds On Fri, 9 Dec 2005 06:10:03 0800, "Ron Coderre" wrote: Try this: For a DNA sequence in Cell A1 B1: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))33) Does that help? *********** Regards, Ron "Rhapsody 1234" wrote: I work with DNA and would like to assign numerical values to the bases. i.e. A = 32 etc. Ideally, I just want to past a DNA sequence into Excel, e.g. ATGCCA and then have a numerical display of the value obtained if these are summed. e.g. if A=32, AA=64, AAA=96 etc. Any way I can do this? Thanks! Richard Buttrey __ 
#6




Convert letters into numbers. i.e. ABCD = 52
Thanks, but....I'm really only letting Excel do its job.
Here's the explanation (Kinda long though...I figured maybe too much info is better than not enough, in this case): =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))33) I want the ASCII code for each letter in the string. To do that, I need to create a array that contains each of those letters. The MID function can extract subsets of strings. =MID("MYWORD",2,1) extracts a 1letter string from MYWORD, beginning with the 2nd character. It returns "Y" If I knew that the string was ALWAYS going to be 6 characters, I could create the array by using: =MID("MYWORD",{1,2,3,4,5,6},1) That would return {"M","Y","W","O","R","D"} I could also write that formula as: =MID("MYWORD",ROW(1:6),1) Note: the ROW(1:6) Returns the array of row numbers associated with rows 1:6....{1,2,3,4,5,6} But, since I don't know how long the text will be, I need to create a dynamic array of numbers from 1 to ???, that is driven by the string length. Consequently, I used the INDIRECT function...which tries to convert its contents into any kind of Excel range reference. So if A1 contains "MYWORD", ROW(INDIRECT("1:"&LEN(A1))) creates a string "1:6", which INDIRECT converts to references to those rows (1:6), for which the ROW function returns the array of row numbers: {1,2,3,4,5,6} This formula: MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) returns the array of letters. I use the UPPER function to bring consistency to the array: the code for "A" is 32, but the code for "a" is 95! Which brings us to the CODE function, which returns the ASCII code for a given letter. The codes for MYWORD are {77;89;87;79;82;68} The OP indicated that A should equate to 32, but the code for A is 65. So I had to subtract 33 from it to get to 32. The new array is: {44;56;54;46;49;35} Last, the SUMPRODUCT returns the sum of the values in that array: 284 I hope that helps *********** Regards, Ron "Richard Buttrey" wrote: That's quite brilliant. Would you mind explaining (in English) how this sumproduct is working please. I thought sumproduct needed at least a couple of arrays, but this only appears to have one. Rgds On Fri, 9 Dec 2005 06:10:03 0800, "Ron Coderre" wrote: Try this: For a DNA sequence in Cell A1 B1: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))33) Does that help? *********** Regards, Ron "Rhapsody 1234" wrote: I work with DNA and would like to assign numerical values to the bases. i.e. A = 32 etc. Ideally, I just want to past a DNA sequence into Excel, e.g. ATGCCA and then have a numerical display of the value obtained if these are summed. e.g. if A=32, AA=64, AAA=96 etc. Any way I can do this? Thanks! Richard Buttrey __ 
#7




Convert letters into numbers. i.e. ABCD = 52
It's a known technique used in many ways, here's another from
Bob Phillips basically using the same technique to get something else http://tinyurl.com/9oh4n  Regards, Peo Sjoblom "Richard Buttrey" wrote in message ... That's quite brilliant. Would you mind explaining (in English) how this sumproduct is working please. I thought sumproduct needed at least a couple of arrays, but this only appears to have one. Rgds On Fri, 9 Dec 2005 06:10:03 0800, "Ron Coderre" wrote: Try this: For a DNA sequence in Cell A1 B1: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))33) Does that help? *********** Regards, Ron "Rhapsody 1234" wrote: I work with DNA and would like to assign numerical values to the bases. i.e. A = 32 etc. Ideally, I just want to past a DNA sequence into Excel, e.g. ATGCCA and then have a numerical display of the value obtained if these are summed. e.g. if A=32, AA=64, AAA=96 etc. Any way I can do this? Thanks! Richard Buttrey __ 
#8




Convert letters into numbers. i.e. ABCD = 52
Why do you use SUMPRODUCT instead of SUM?
"Ron Coderre" wrote: Thanks, but....I'm really only letting Excel do its job. Here's the explanation (Kinda long though...I figured maybe too much info is better than not enough, in this case): =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))33) I want the ASCII code for each letter in the string. To do that, I need to create a array that contains each of those letters. The MID function can extract subsets of strings. =MID("MYWORD",2,1) extracts a 1letter string from MYWORD, beginning with the 2nd character. It returns "Y" If I knew that the string was ALWAYS going to be 6 characters, I could create the array by using: =MID("MYWORD",{1,2,3,4,5,6},1) That would return {"M","Y","W","O","R","D"} I could also write that formula as: =MID("MYWORD",ROW(1:6),1) Note: the ROW(1:6) Returns the array of row numbers associated with rows 1:6....{1,2,3,4,5,6} But, since I don't know how long the text will be, I need to create a dynamic array of numbers from 1 to ???, that is driven by the string length. Consequently, I used the INDIRECT function...which tries to convert its contents into any kind of Excel range reference. So if A1 contains "MYWORD", ROW(INDIRECT("1:"&LEN(A1))) creates a string "1:6", which INDIRECT converts to references to those rows (1:6), for which the ROW function returns the array of row numbers: {1,2,3,4,5,6} This formula: MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) returns the array of letters. I use the UPPER function to bring consistency to the array: the code for "A" is 32, but the code for "a" is 95! Which brings us to the CODE function, which returns the ASCII code for a given letter. The codes for MYWORD are {77;89;87;79;82;68} The OP indicated that A should equate to 32, but the code for A is 65. So I had to subtract 33 from it to get to 32. The new array is: {44;56;54;46;49;35} Last, the SUMPRODUCT returns the sum of the values in that array: 284 I hope that helps *********** Regards, Ron "Richard Buttrey" wrote: That's quite brilliant. Would you mind explaining (in English) how this sumproduct is working please. I thought sumproduct needed at least a couple of arrays, but this only appears to have one. Rgds On Fri, 9 Dec 2005 06:10:03 0800, "Ron Coderre" wrote: Try this: For a DNA sequence in Cell A1 B1: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))33) Does that help? *********** Regards, Ron "Rhapsody 1234" wrote: I work with DNA and would like to assign numerical values to the bases. i.e. A = 32 etc. Ideally, I just want to past a DNA sequence into Excel, e.g. ATGCCA and then have a numerical display of the value obtained if these are summed. e.g. if A=32, AA=64, AAA=96 etc. Any way I can do this? Thanks! Richard Buttrey __ 
#9




Convert letters into numbers. i.e. ABCD = 52
Actually, you CAN use the SUM in place of the SUMPRODUCT function.....but
you'll need to commit the resulting array formula by holding down [Ctrl]+[Shift] when you press [Enter]. My general preference is to use SUMPRODUCT. Particularly, if the workbook will be used by others. The reason: If somebody who is inexperienced with array formulas edits the array formula....they'll never guess to [Ctrl]+[Shift]+[Enter]. Invariably, I get the call that the workbook is "broken". That problem is avoided by using the SUMPRODUCT function. Does that make sense? *********** Regards, Ron "Sloth" wrote: Why do you use SUMPRODUCT instead of SUM? "Ron Coderre" wrote: Thanks, but....I'm really only letting Excel do its job. Here's the explanation (Kinda long though...I figured maybe too much info is better than not enough, in this case): =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))33) I want the ASCII code for each letter in the string. To do that, I need to create a array that contains each of those letters. The MID function can extract subsets of strings. =MID("MYWORD",2,1) extracts a 1letter string from MYWORD, beginning with the 2nd character. It returns "Y" If I knew that the string was ALWAYS going to be 6 characters, I could create the array by using: =MID("MYWORD",{1,2,3,4,5,6},1) That would return {"M","Y","W","O","R","D"} I could also write that formula as: =MID("MYWORD",ROW(1:6),1) Note: the ROW(1:6) Returns the array of row numbers associated with rows 1:6....{1,2,3,4,5,6} But, since I don't know how long the text will be, I need to create a dynamic array of numbers from 1 to ???, that is driven by the string length. Consequently, I used the INDIRECT function...which tries to convert its contents into any kind of Excel range reference. So if A1 contains "MYWORD", ROW(INDIRECT("1:"&LEN(A1))) creates a string "1:6", which INDIRECT converts to references to those rows (1:6), for which the ROW function returns the array of row numbers: {1,2,3,4,5,6} This formula: MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) returns the array of letters. I use the UPPER function to bring consistency to the array: the code for "A" is 32, but the code for "a" is 95! Which brings us to the CODE function, which returns the ASCII code for a given letter. The codes for MYWORD are {77;89;87;79;82;68} The OP indicated that A should equate to 32, but the code for A is 65. So I had to subtract 33 from it to get to 32. The new array is: {44;56;54;46;49;35} Last, the SUMPRODUCT returns the sum of the values in that array: 284 I hope that helps *********** Regards, Ron "Richard Buttrey" wrote: That's quite brilliant. Would you mind explaining (in English) how this sumproduct is working please. I thought sumproduct needed at least a couple of arrays, but this only appears to have one. Rgds On Fri, 9 Dec 2005 06:10:03 0800, "Ron Coderre" wrote: Try this: For a DNA sequence in Cell A1 B1: =SUMPRODUCT(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LE N(A1))),1)))33) Does that help? *********** Regards, Ron "Rhapsody 1234" wrote: I work with DNA and would like to assign numerical values to the bases. i.e. A = 32 etc. Ideally, I just want to past a DNA sequence into Excel, e.g. ATGCCA and then have a numerical display of the value obtained if these are summed. e.g. if A=32, AA=64, AAA=96 etc. Any way I can do this? Thanks! Richard Buttrey __ 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
convert numbers to text  Excel Discussion (Misc queries)  
Auto convert an alphanumeric string (CIS9638S) to numbers only?  Excel Worksheet Functions  
Convert text to numbers  Excel Worksheet Functions  
How do I sort letters before numbers in Excel?  Excel Discussion (Misc queries)  
How do I convert numbers stored as text with spaces to numbers  Excel Discussion (Misc queries) 