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.


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


Then in B2:


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.