EXACT function - but where does it not match?

  • Thread starter Thread starter ana
  • Start date Start date
A

ana

I'm currently using the EXACT function to compare two groups of text.

Is there a macro I can write or function I can use where Excel can tell me,
no it's not an exact match and highlight or format where the text starts to
not match.

So it matches up until it gets to the 255th character, and maybe bolds in
red at the start of non-exact-match.

Please note I'm using Exact b/c the spacing, text, everything within the
cell must exactly match.
 
You cannot format only a part of a cell's contents. Formatting is all
or nothing. You can format the entire cell's content or none of it,
but nothing in between.

The following array formula will return the number of characters, left
to right, that match between A1 and B1. E.g., if A1 is "abcde" and B1
is "abcxyz", the result is 3, meaning that the first 3 left characters
match and the mismatch begins at position 4. This is a case sensitive
match ("a" <> "A").

=MAX(IF(EXACT(MID(A1,ROW(INDIRECT("1:"&MIN(LEN(A1),LEN(B1)))),1),
(MID(B1,ROW(INDIRECT("1:"&MIN(LEN(A1),LEN(B1)))),1))),
ROW(INDIRECT("1:"&MIN(LEN(A1),LEN(B1)))),"FALSE"))

If you want to ignore case ("a" = "A") then use the following array
formula:

=MAX(IF(MID(A1,ROW(INDIRECT("1:"&MIN(LEN(A1),LEN(B1)))),1)=
(MID(B1,ROW(INDIRECT("1:"&MIN(LEN(A1),LEN(B1)))),1)),
ROW(INDIRECT("1:"&MIN(LEN(A1),LEN(B1)))),"FALSE"))

Note that these formulas are line split for readability. In Excel, the
formulas should be on a single line.

These are array formulas, so you MUST press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit
it later. If you do this correctly, Excel will display the formula in
the formula bar enclosed in curly braces { }. You don't type in the
braces; Excel puts them there automatically. The formula will not work
correctly if you do not enter it with CTRL SHIFT ENTER. See
www.cpearson.com/Excel/ArrayFormulas.aspx for much more information
about array formulas.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
 
Try this array formula

=MIN(IF(NOT(EXACT(MID(F1,ROW(INDIRECT("1:"&LEN(F1))),1),MID(G1,ROW(INDIRECT("1:"&LEN(G1))),1))),ROW(INDIRECT("1:"&LEN(F1)))))

it will show 0 if the mtach exactly
 
No luck, it's showing a 0 when it doesn't match, it also forced a copy of the
cell that I have the array in, into the one below (really odd).

And I did use the parenthesis w/ the array.
 
'/===============================================/
' Function Purpose: Compare where words differ
'/===============================================/
'
Public Function GetNonMatch(Select_1 As Range, _
Compare_2 As Range) As String
Dim i As Integer
Dim sResult As String

On Error GoTo err_Function

Application.Volatile

If Len(Select_1.Value) = 0 Then
sResult = Compare_2.Value
GoTo exit_Function
End If

For i = 1 To Len(Select_1.Value)
If Mid(Select_1.Value, i, 1) <> _
Mid(Compare_2.Value, i, 1) Then
sResult = i & " - " & _
Right(Select_1.Value, Len(Select_1.Value) - i + 1)
Exit For
End If
Next i

If Len(Compare_2.Value) > Len(Select_1.Value) And _
Select_1.Value = _
Left(Compare_2.Value, Len(Select_1.Value)) Then
sResult = "* - " & Right(Compare_2.Value, _
Len(Compare_2.Value) - Len(Select_1.Value))
End If

exit_Function:
On Error Resume Next
GetNonMatch = sResult
Exit Function

err_Function:
sResult = ""
GoTo exit_Function

End Function
'/===============================================/
 
If you want the formula to work also when the two texts are of
different lenght you have to extend it a bit. Try this:

=MIN(IF(NOT(EXACT(MID(F1,ROW(INDIRECT("1:"&MAX(LEN(F1),LEN(G1)))),1),MID(G1,ROW(INDIRECT("1:"&MAX(LEN(F1),LEN(G1)))),1))),ROW(INDIRECT("1:"&MAX(LEN(F1),LEN(G1))))))

Note: This is an array formula that must be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Åke
 
You cannot format only a part of a cell's contents. Formatting is all
or nothing. You can format the entire cell's content or none of it,
but nothing in between.

The following array formula will return the number of characters, left
to right, that match between A1 and B1. E.g., if A1 is "abcde" and B1
is "abcxyz", the result is 3, meaning that the first 3 left characters
match and the mismatch begins at position 4. This is a case sensitive
match ("a" <> "A").

=MAX(IF(EXACT(MID(A1,ROW(INDIRECT("1:"&MIN(LEN(A1),LEN(B1)))),1),
(MID(B1,ROW(INDIRECT("1:"&MIN(LEN(A1),LEN(B1)))),1))),
ROW(INDIRECT("1:"&MIN(LEN(A1),LEN(B1)))),"FALSE"))

If you want to ignore case ("a" = "A") then use the following array
formula:

=MAX(IF(MID(A1,ROW(INDIRECT("1:"&MIN(LEN(A1),LEN(B1)))),1)=
(MID(B1,ROW(INDIRECT("1:"&MIN(LEN(A1),LEN(B1)))),1)),
ROW(INDIRECT("1:"&MIN(LEN(A1),LEN(B1)))),"FALSE"))

Note that these formulas are line split for readability. In Excel, the
formulas should be on a single line.

These are array formulas, so you MUST press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit
it later. If you do this correctly, Excel will display the formula in
the formula bar enclosed in curly braces { }. You don't type in the
braces; Excel puts them there automatically. The formula will not work
correctly if you do not enter it with CTRL SHIFT ENTER. See
www.cpearson.com/Excel/ArrayFormulas.aspx for much more information
about array formulas.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
 
Did you array-enter it?

--

HTH

Bob

ana said:
No luck, it's showing a 0 when it doesn't match, it also forced a copy of
the
cell that I have the array in, into the one below (really odd).

And I did use the parenthesis w/ the array.
Click to expand...
 
The following macro will return the character position number at which the
two strings stop EXACT matching each other...

Function NoMatch(Text1 As String, Text2 As String) As Long
Application.Volatile
For NoMatch = 1 To WorksheetFunction.Max(Len(Text1), Len(Text2))
If Not Mid(Text1, NoMatch, 1) Like Mid(Text2, NoMatch, 1) Then Exit For
Next
End Function
 
Back
Top