# Need Function to compare two columns for matching SUBSTRING values

• ### Question

• Need Function to compare two columns for matching SUBSTRING values. Let me explain. I have column A and column B. They both will have some text. But they will both have "m1" or "m2" or "m3" ... up to "m25"  somewhere in them. I want to find the mismatches.

Is this small example I am interested in identifying row 3. Column A has M12, but Column B has M1. This is a mismatch. They both need to contain the same M99 value to be a match.

Column A,B

HBI_M2, TableM2
HBI_M3_xx, TableM3
HBI_M12, TableM1

Can someone help me get started on this? I am not too good with Excel Functions. Thanks.

Friday, July 24, 2015 2:39 PM

• Create the following function in a module in the Visual Basic Editor:

Function Extract(s As String) As String
Dim p As Long
Dim q As Long
p = InStr(s, "M")
If p > 0 Then
q = p
Do While q < Len(s)
If IsNumeric(Mid(s, q + 1, 1)) Then
q = q + 1
Else
Exit Do
End If
Loop
Extract = Mid(s, p, q - p + 1)
End If
End Function

Enter the following formula in C1:

=Extract(A1)=Extract(B1)

and fill down to the end of the data. You'll get TRUE if the Mnn values match, FALSE otherwise.

Regards, Hans Vogelaar (http://www.eileenslounge.com)

• Proposed as answer by Friday, July 24, 2015 4:00 PM
• Marked as answer by Saturday, July 25, 2015 6:40 PM
Friday, July 24, 2015 3:58 PM

### All replies

• Create the following function in a module in the Visual Basic Editor:

Function Extract(s As String) As String
Dim p As Long
Dim q As Long
p = InStr(s, "M")
If p > 0 Then
q = p
Do While q < Len(s)
If IsNumeric(Mid(s, q + 1, 1)) Then
q = q + 1
Else
Exit Do
End If
Loop
Extract = Mid(s, p, q - p + 1)
End If
End Function

Enter the following formula in C1:

=Extract(A1)=Extract(B1)

and fill down to the end of the data. You'll get TRUE if the Mnn values match, FALSE otherwise.

Regards, Hans Vogelaar (http://www.eileenslounge.com)

• Proposed as answer by Friday, July 24, 2015 4:00 PM
• Marked as answer by Saturday, July 25, 2015 6:40 PM
Friday, July 24, 2015 3:58 PM
• Hans, your post definately got me started. I did not use EXTRACT as the function name, that seemed to be a problem -  maybe a reserved word? There were also a couple of other gotchas. This is what I ended up with.

=IF(PPExtract(INDIRECT("\$A" & ROW()))=PPExtract(INDIRECT("\$B" & ROW())),  PPExtract(INDIRECT("\$A" & ROW())),  PPExtract(INDIRECT("\$A" & ROW())) & "/" & PPExtract(INDIRECT("\$B" & ROW())))

Public Function PPExtract(s As String) As String
Dim p As Long
Dim q As Long
Dim freqm As String

freqm = "M"

q = 25

Do While q > 0
If InStr(s, freqm & CStr(q)) > 0 Then
If q < 10 Then
PPExtract = Mid(s, InStr(s, freqm & CStr(q)), 2)
Exit Function
End If
PPExtract = Mid(s, InStr(s, freqm & CStr(q)), 3)
Exit Function
End If
q = q - 1
Loop