Excel help

[Mo0o]

The lab im working at is trying to create a excel spreadsheet. What i gotta do is convert a RNA string like CAA would be converted to GTT because C-G are paired and A-T. Basically i was wondering it can be done that excel reads a string of letters in one cell and creates a string of letters in another cell based on a certain set of parameters (C goes to G, vice versa, A goes to T, vice versa).

any help would be appreciated.

 

[dullard]

What I would do is to export it to a text file, write a little code yourself to do the conversion, and to import it back it. Excel has limited programming capability as well in the Tools/Macro menu (so you wouldn't have to bother with exporting and importing the code) but I've never played much with the new Excel Macros.

 

[Electric Amish]

Originally posted by: dullard
What I would do is to export it to a text file, write a little code yourself to do the conversion, and to import it back it. Excel has limited programming capability as well in the Tools/Macro menu (so you wouldn't have to bother with exporting and importing the code) but I've never played much with the new Excel Macros.

They're not too bad. Mainly VBscript.

 

[dullard]

Wait, I think there may be an easier method. I'll be right back.

1) Put the string into cell A1. For example use AATCGGT
2) Make a column in B counting from 1 to the total length of the string (excel has a len() function to let you know this number). In my exampe that column would look like (B1: 1, B2: 2, ... , B7: 7).
3) Make a Column in C which finds the next letter. Enter this in cell C1 and copy down: =MID(A$1,b1,1)
4) Do an if statement in Column D to do your conversion. Enter this in cell D1 and copy down: =IF(C2="A","T",(IF(C2="T","A",(IF(C2="C","G",(IF(C2="G","C")))))))

Note: add more letters if you wish for the more unusual bases.

5) Combine them back. Type this formula into your desired location: =D1&D2&D3&

Keep going until you finish.

 

[minendo]

Originally posted by: dullard
Wait, I think there may be an easier method. I'll be right back.

Edit --> Replace. Just keep a copy of the original strand in another sheet.

Or you could use a nested if statement.

 

[dullard]

Originally posted by: minendo

Originally posted by: dullard
Wait, I think there may be an easier method. I'll be right back.

Edit --> Replace. Just keep a copy of the original strand in another sheet.

Or you could use a nested if statement.

Ok I guess that is even easier. Be careful with the Edit/Replace though. You first must replace everything with dummy letters, and then convert the dummy letters into the desired letters (making it quite difficult if you begin to use all the unusual bases since you'll soon run out of letters, and you have to use symbols and remember what symbol is what letter, etc)

For example if you had this: AATTCG and replaced the A's with T's you'd get this: TTTTCG. Note how it is now messed up, thus the reason for dummy letters.

 

[HomeBrewerDude]

use the Lookup function.

 

[FuzzyBee]

use a lookup table. these are the value that you want to put in the table:

65 T
67 G
71 C
84 A

copy this function down your string (replacing, of course, the cell references):

=LOOKUP(CODE(F13),$A$1:$A$4,$B$1:$B$4)