I have cell values with names that are fairly close to each other. I would like to extract a common sub-string.

Here is an example.

1100_250_Jump_12HR_100MD_S_run1 
1100_250_Jump_12HR_100MD_S_run2
1100_250_Jump_12HR_100MD_S_run3 
1100_250_Jump_12HR_100MD_S_run4 
1101_250_Jump_12HR_100MD_U_run5 
1101_250_Jump_12HR_100MD_U_run6 
1102_250_Jump_12HR_100MD_U_run7 
1102_250_Jump_12HR_100MD_U_run8 
1102_250_Jump_12HR_100MD_U_run9 
1102_250_Jump_12HR_100MD_U_run10

The output should be _250_Jump_12HR_100MD_

How can I extract a common sub-string in Excel?

I checked similar questions but could not find the answer.

  • Just to be certain, I assume you don't know ahead of time what the substring is? How small or large is an acceptable substring? in your sample data, would 100MD be long enough? Will the matching substring always start in the same position (6th character in your sample data)? Also, can you share what you have tried so far? – cybernetic.nomad Dec 6 at 21:03
  • You may find this to be of interest towards reaching a solution – cybernetic.nomad Dec 6 at 21:16
  • No. I don't know the substring ahead of time. Also, the length of it is not fixed. It definitely not too long. The substring does not always start at the same position. I have tried to play around with UNIQUE, MID, but with no success at all. – Cricrazy Dec 6 at 21:17

Based on the UDF in the link I posted in comments:

Option Explicit


Public Function CSTMatch3(Target1 As Range, Target2 As Range) As String

CSTMatch3 = ""

Dim myString As String, String1 As String, String2 As String, i As Long, j As Long, noChar As Long

noChar = 0

'The goal here is to assign the larger String to the variable String1
If Target1 = Target2 Then
    CSTMatch3 = Target1
    Exit Function
End If

If Len(Target1) >= Len(Target2) Then
    String1 = Target1
    String2 = Target2
Else
    String1 = Target2
    String2 = Target1
End If

For j = 1 To Len(String2)
    For i = 1 To Len(String1) - j
        If InStr(String2, Mid(String1, i, j)) Then
            myString = Mid(String1, i, j)
            noChar = noChar + 1
            Exit For
        End If
    Next i
Next j

CSTMatch3 = myString

End Function

Then, assuming you strings are in column A, Use the UDF in B1 like this:

=CSTMatch3(A1,A2)

Then in B2:

=CSTMatch3(A2,B1)

And populate down.

Cells in column B in any given row now show the common string for all cells in A up to that row.

enter image description here

  • Thanks a lot. Will go through the code. – Cricrazy Dec 6 at 22:27

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.