EXACT function - but where does it not match?

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.
 
C

Chip Pearson

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
 
B

Bob Phillips

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
 
A

ana

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.
 
G

Gary Brown

'/===============================================/
' 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
'/===============================================/
 
L

Lars-Åke Aspelin

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
 
C

Chip Pearson

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
 
B

Bob Phillips

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...
 
R

Rick Rothstein

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Easy Problem b/c I am not an Excel Expert 4
Userform combobox: best match on partially typed content? 4
Matching literal text "???" in Word 2010 field code IF statement 2
VLOOKUP FUNCTION 3
Matching Column Data 1
Using VLookup when text isn't an exact match 3
Create if partial match then copy and paste data from one cell to another 1
Syntax help please 7

Top