Changing letters in a text string

[scbody]

Hi

I have text strings consisting of about 500 letters consisting of A, C, G and T. I need to switch A-->T, C-->G, G-->C and T-->A across the whole string. I've tried searching for methods and several techniques without success.
I bet there is a simple technique, but I don't have it!
Can anyone help?

Many thanks
Simon

 

[taigovinda]

Welcome to the board!

You can find a character that doesn't show up in any of the text strings, like maybe ^

Then, use control+H (find and replace) and play musical chairs. T->^, then A->T, then ^->A, then G->^, then C->G, then ^->C.

Hope that helps.

Tai

 

[James_B]

That sounds like a pretty serious genetic mutation!

 

[scbody]

Hi Tai

Yes, I've been doing that for smaller spreadsheets but I've got a bunch (>1000) of data to do it to, so I was hoping to do it with some code.
Thanks

Simon

 

[scbody]

Hi James

Fortunately its not a mutation. Its only a method of creating the minus strand, then I have to do exact match Blasts of a short string.

Thanks
Simon

 

[James_B]

Fortunately its not a mutation.

Glad to hear it. You could try putting the code from this post (which by pure co-incidence is one of mine) into a module and then running a routine like

public sub TryThis
   dim rData as range
   set rData = DesiredSheet.Usedrange
   RngRepWith rData, array("A", "C", "G", "T"), array(1, 2, 3, 4)
   RngRepWith rData, array(1, 2, 3, 4), array("T", "G", "C", "A")
end sub

It's not the most elegant solution. But it's late here and it should do the job.

 

[taigovinda]

What's wrong with just clicking "replace all?" And recording the replace all if needed to run on multiple sheets? Wouldn't built-in functionality be faster than code, or am I missing something here?

 

[Moleo]

It could be done without code by performing sequential substitutions feeding the output of one operation into another until all are done.
eg Mask original values A > B , C > D , G > H , T > U
then B > T , D > G , H > C , U > A

 

[scbody]

James - thank you for the code. I'll implement it today and let you know the results.
Tai - for one spreadsheet its easier to do it your way. For >1 spreadsheet (of which I have many) the code is more reliable and quicker.
Many thanks to you both
Simon

 

[James_B]

Wouldn't built-in functionality be faster than code, or am I missing something here?

All things being equal, yes. But I suspect that all things aren't in fact equal here since the code only has to loop through the range twice (and check each value in a dictionary, which is quick anyway since dictionaries use double linked lists) rather than 8 times. Feel free to run some tests though. I'm always happy to be proven wrong (actually, that's a complete lie, but you know what I mean)