Search code examples
excelfunctionmatchstring-comparison

Compare two string values and return the common string

This is what I would like to do in Excel.

A1 = Hello my name is John B1 = Hello my name is Joe

C1 = Hello my name is

A2 = Where is John going out tomorrow? B2 = Where is Joe going out tomorrow?

C1 = Where is

As you can see, I want to compare 2 cells and return the common string until there is a difference. (Stop the comparison as soon there is a change)

I have seen something similar here but is it slightly different from my request.

Thanks in advance.

Solution

  • You should search for a more efficient way but here's one in VBA:

    Dim stringA As String
Dim stringB As String

Dim finalString As String

Dim currentLetter As Integer

For currentLetter = 0 To Len(stringA)
    If Left(stringA, currentLetter) = Left(stringB, currentLetter) Then
        finalString = Left(stringA, currentLetter)
        Exit For
    End If
Next

    Replace the string variables by your cells and it's done.