Finding every occurance of a string

We are doing DNA research. We need to find all starting positions of a discreet string of characters within a larger string of characters. Below is a sample source string. Using FIND, we note that the first instance of TATAA from the left is at character 286. I think we can use the AGGREGATE formula (15 Small and 6 Ignore hidden values) to present all instances but I can't make it work. The ideal result would actually display the results from the right end of the source string. A result might be 6, 50, 75 (though that is not the case below). It doesn't matter if the list of occurences is presented in a column or row.

If you find a great answer, then you will be helping figure out one of the great mysteries of life.

Sample string (in cell B9)
GGGAGTGTTATTTTAATAAAATTGCTAGTTATTGTTGTAACGCATTCGTCTTATTCGAATCAAAACGACAATTTCTTCCTACTCAGTGTACATACATGAAAAAAATAACTGTTTAATACACGAAGGAAACACATTAACAATTCTGTTGCGAAAAATATAGCGTTGTAAATTTTGACATACGCTTATTTTTTAAAATAAGAAAGACGATTTCTCTATTTTTCTTGATATTTATCCTTCCGCAATTGTCGTGACAATTTGTTCTCTCCTCTTCTTCAAGGCAACGATTATAACACAGTTATTTGAATATCACTCAAGTGTAACGGTTTCGAGCTCTTCGATATTTTCACGTAGCACGCGCCTGTGATGTTCTAGTTCTTAGAAAAACATATAATAACGGAAAAACGTCTCACCTTTCGTTGTTCCGTTTTTCTGAATCTGTACTCGACACCCTGTACACCTTCATTACTTTCAATGCAATTCGGTTGTTCATACCAATATCACATTTCATATGGACTCTTATATGTCAACGATTTAGCGTAAAAGCATTATTCACTGCACTTCTGATGACGATGCGCGAATTAAATCTATAACAAACGTTGCATCAAAAACGTTGAGTTAAACAAAATATCGCGCAATTGAGACATAAACAAAAAAAAAACGAGATATTTTTGTCCATTTATTACGAAAGTAACTCGCGATGTTCTTCCGTCATTTGTAAGACAAGACATGTAATAATTTTTCTCTTACTGTTCGTGTGCGATATGAGCGCAACTGTGGAGTTAGATAGGTAGCGATGTACAAAACGTAACCATATAGTTTCTTGGTCAGAACAATTTGACACAATAGAGCACTTCTCCGCATTTTGCACTAATGCGTGCGTAAAGACGGGTTGACAAGCGGGTATACGTCCCGAGCTGAATAAAAAATAGATCCTGATACTTGCGTCGCGTCGACGTAGCGAGAGCGGGATCGGTTATTGACGACAGCTAGTAGTTAGTCA

Hi William,

See the attached with this formula to do what you request:

Formula:

Please Login or Register  to view this content.

Put any DNA string in B4 and longer string in B6 and the formula should work. There might be a limit for the length of the string in B6.

DNA Strings Location.xlsx

This returns a different value.

Using search string from post 1 TATAA in B4 and with the target string in B9. Returns 3.

Formula:

Please Login or Register  to view this content.

Attached is a Power Query solution for those using Excel 2010 or newer.

I used this tutorial to incorporate the search letter into the query.

All that you have to do once this is created is change the value in the Search Letter cell and refresh the green table.

Originally Posted by WilliamWelch

Thank you. Those tell me the number of instances. What I'm looking for is the position with in the string. For example:

abcabcabc

If searching for "c", the result would be positions 3,6, and 9 from the left side or 1,4,7 from the right side (preferred).

My apologies. I read it wrong.

If you are still interested in a formula solution in the attached please find one requiring a helper row. BTW the DNA string had an extra space. It is removed in the attached.

In B5 filled across returns the positions --- left to right.

Formula:

Please Login or Register  to view this content.

In B6 filled across references the above returns positions right to left.

Formula:

Please Login or Register  to view this content.

Here's another. No helpers. Since you mentioned AGGREGATE I used it.

BTW: Please update your profile. AGGREGATE is a newer function (2010).

Formula:

Please Login or Register  to view this content.