Changing letters in a text string

scbody

New Member
Joined
Oct 1, 2009
Messages
4
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
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
 

scbody

New Member
Joined
Oct 1, 2009
Messages
4
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

New Member
Joined
Oct 1, 2009
Messages
4

ADVERTISEMENT

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

Active Member
Joined
Jun 11, 2009
Messages
447
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

Code:
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

Well-known Member
Joined
Mar 28, 2007
Messages
2,639

ADVERTISEMENT

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

New Member
Joined
Oct 3, 2009
Messages
2
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

New Member
Joined
Oct 1, 2009
Messages
4
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

Active Member
Joined
Jun 11, 2009
Messages
447
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)
 
Last edited:

Forum statistics

Threads
1,136,328
Messages
5,675,137
Members
419,551
Latest member
thangxpm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top