[SOLVED] Compare two string values and return the common string

Issue

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.

Answered By – e_jalbert

Answer Checked By – Mary Flores (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.