String manipulation in Excel 1

[BrCo]

Dear experts,
I have a difficult task, and would appreciate your help.
In Excel, I have in each cell a DNA sequence which is a string composed of the letters a,g,c,t. For example:

cacacaaggggtgaagcttgcggcttaatggagtcaacgccggaaacctcacccggggcgacagcaggatgaagccaggctaacgaccttgccggacgagctgagaggaggtgcatggccgtcgtcagctcgtgc

What I need to do is:
1) find if this string contains in it the 1st "mini-string", aagtgaac.
2) find if this string contains in it the 2nd "mini-string", gcgcttatt.
3) IF both mini-strings are present, then COPY the part of the big string that is in the middle, i.e. bound between the two mini-strings.

Many thanks in advance!
BrCo

 

[GregLocock]

Have you looked at the help for "FIND", and "MID"?

Cheers

Greg Locock

SIGlease see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[BrCo]

I have, but the number of characters to copy (with MID) is variable, so I'm not sure how to do it.

 

[cowski]

You can use the SEARCH and MID functions to get what you want.

If your search string is in cell A1, first 'mini-string' is in B1, and second 'mini-string' is in C1 then try this formula (in the cell where you want the extracted sequence):

=MID(A1,SEARCH(B1,A1)+LEN(B1),SEARCH(C1,A1))

Of course, you can modify A1, B1, and C1 as necessary in the formula.

 

[cowski]

Oh, one other thing: the SEARCH function is not case sensitive while the FIND function is. You can use either SEARCH or FIND in the formula given above based on your needs.

 

[cowski]

Forgot to mention the limitations. If either of the search strings are not present in the main string, the #VALUE! error will be returned. Also, the function given above will only find the first occurrance of each 'mini-string'. It will need to be modified if there is the possibility of multiple occurrances within the main string.

 

[IRstuff]

Is this for school?

TTFN

FAQ731-376