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(B 1)))),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




On Fri, 19 Mar 2010 09:07:03 -0700, ana
wrote:

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.