Hello stranula,
Important when you put your examples that they are realistic.
Assuming that they are, it means that you already know where to find the common starting string. It is always the 19 first characters. Then you could do it like this...
Public Sub FindCommonSimple()
MsgBox Mid(Range("A1").Value, 1, 19), vbOKOnly, "Longest common name"
End Sub
Or even more simple, with a formula somewhere on your sheet.
Assuming that your examples are not realistic and that the common starting string could vary in length between datasets, then something like this could work (I am sure someone here could do it better, but it works
)
Public Sub FindCommonComplicated()
tmpName = ""
prevName = ""
i = 1
Do Until Range("A" & i).Value = ""
tmpName = Range("A" & i).Value
comName = ""
If prevName = "" Then
comName = tmpName
GoTo NextName
Else
End If
charCount = Len(prevName)
If Len(tmpName) < Len(prevName) Then charCount = Len(tmpName)
For x = 1 To charCount
If Mid(tmpName, x, 1) = Mid(prevName, x, 1) Then
comName = comName & Mid(tmpName, x, 1)
Else
Exit For
End If
Next x
NextName:
prevName = comName
i = i + 1
Loop
MsgBox prevName, vbOKOnly, "Longest common name"
End Sub
Let us know if this helped you or if you need something else.
Good luck!
Bookmarks