×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

String manipulation in Excel

String manipulation in Excel

String manipulation in Excel

(OP)
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

RE: String manipulation in Excel

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

Cheers

Greg Locock

SIG:Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: String manipulation in Excel

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

RE: String manipulation in Excel

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

CODE

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

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

RE: String manipulation in Excel

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.

RE: String manipulation in Excel

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources