Excel for DNA Sequencing analysis - how can I find a string of text within a large string?

A TGCTGTGTTGTGGATGAATTAGATGCAG
B GCAGATCTCAAAAGAAGGAGTACTCCGAA

1 TGCTGTGTTGTGGATGAATTAGATGCAGATCTCAAAAGAAGGAGTACTCCGAA
2 TGCTGTGTTGTGGATGAATTAGATCTCAAAAGAAGGAGTACTCCGAA
3 TGCTGTGTTGTGGATGAATTAGTCAAAAGAAGGAGTACTCCGAA
4 TGCTGTGTTGTGGATCTCAAAAGAAGGAGTACTCCGAA
5 TGCTGTGTTGTGGATGAATTAGATGCATCTCAAAAGAAGGAGTACTCCGAA
6 TGCTGTGTTGTGGATGAAGGAGTACTCCGAA
7 TGCTGTGTTGTGGATGAATTAGAAGGAGTACTCCGAA
8 TGCTGTGTTGTGGATGATCTCAAAAGAAGGAGTACTCCGAA
9 TGCTGTGTTGTGGATGAA


I want to find how much of Text String A is in Text String 1 starting from Left to Right. Then I want to find out how much of Text String B is in Text String 1 from right to left. So on and so forth for 1-9. Any ideas?

I don't understand:

When Text String A ends I want to find where Text String 1 it picks up in Text String B, also from left to right. So on and so forth for 1-9. Any ideas?

Ok Try this.

i wrote this as a starter.

Sub macro1()
For Acount = 2 To 3
For BCount = 2 To 10

MaxCount = 0
MaxPos = 0
A = Cells(Acount, 1).Value
B = Cells(BCount, 3).Value

For Offset = 0 To Len(B) - 1
Counter = 0

For Count = 1 To Len(A)
T1 = Mid(A, Count, 1)
t2 = Mid(B, Count + Offset, 1)
If Mid(A, Count, 1) = Mid(B, Count + Offset, 1) Then Counter = Counter + 1
Next

If Counter > MaxCount Then MaxCount = Counter: MaxPos = Offset
Next
Cells(BCount, Acount * 2).Value = MaxCount
Cells(BCount, Acount * 2 + 1).Value = MaxPos
Next
Next
End Sub

My outputs are in columns D & E and F & G easily expanded

Column D gives you the max number of matches of the string in A1 in the strings in column C
Column E tells you where the best Match Ocurred. I suppose I could switch all non matching text to lower case...........

Column F gives you the max number of matches of the string in A2 in the strings in column C
Column G tells you where the best Match Ocurred. I suppose I could switch all non matching text to lower case...........

Ok This code is better as it colour codes your matches.

Sub macro1()
For Acount = 2 To 3
For BCount = 2 To 10

MaxCount = 0
MaxPos = 0
A = Cells(Acount, 1).Value
B = Cells(BCount, 3).Value

For Offset = 0 To Len(B) - 1
Counter = 0

For Count = 1 To Len(A)
T1 = Mid(A, Count, 1)
t2 = Mid(B, Count + Offset, 1)
If Mid(A, Count, 1) = Mid(B, Count + Offset, 1) Then Counter = Counter + 1
Next

If Counter > MaxCount Then MaxCount = Counter: MaxPos = Offset
Next
Cells(BCount, Acount * 2).Value = MaxCount
Cells(BCount, Acount * 2 + 1).Value = MaxPos + 1
If Acount = 2 Then
Cells(BCount, 3).Characters(MaxPos + 1, MaxCount).Font.Color = RGB(0, 0, 225)
Else
Cells(BCount, 3).Characters(MaxPos + 1, MaxCount).Font.Color = RGB(255, 0, 255)
End If
Next
Next
End Sub

I looked at a simple set of vba loops to do this, however, there is an issue where the end part of your "A" string is the same as the first part of your "B". Therefore it is not possible to discern whether you are finding the end of "A" or the start of "B". Are the examples you gave the actual strings, or are they made up examples?
Dan