Finding the character where two strings do not match

On Fri, 11 Jun 2010 16:18:02 +0000, JordanG1 wrote:

>

>

>I have 2 cells B2 and C2 with strings.

>

>I can tell whether they match or not easily enough. But I am not sure how to identify the character where the two strings diverge. Can it be done with a formula (ideas?) or do I need to create a new one (eh?)?

>

>Thanks all. :)

Here's a different approach using a VBA Macro.

It will color the characters in the strings in columns B & C starting

at the place where they don't match.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.

Ensure your project is highlighted in the Project Explorer window.

Then, from the top menu, select Insert/Module and

paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select

the macro by name, and <RUN>.

It will automatically look at all the entries in column B from B2 to

the lowest row, and compare that with the corresponding entries in

Column C.

One limitation is that it will only alter actual strings. It will not

work on strings that are the result of formulas.

==========================================

Option Explicit

Sub StringDiff()

'Higlights string starting where they differ

Dim rg As Range, c As Range

Dim i As Long

Dim s1 As String, s2 As String

'This is just one of many ways to set up the

' range to check

Set rg = Range("B2", Cells(Rows.Count, "B").End(xlUp))

'Anything in the range?

If rg(1, 1).Address = "$B$1" Then

MsgBox ("No data in column B")

Exit Sub

End If

For Each c In rg

s1 = c.Text: s2 = c(1, 2).Text

'to change any formula produced strings to

'actual strings.

'comment next line out if that is not desired

c.Value = s1: c(1, 2).Value = s2

c.Font.Color = vbBlack

c(1, 2).Font.Color = vbBlack

For i = 1 To IIf(Len(s1) > Len(s2), Len(s1), Len(s2))

If Mid(s1, i, 1) <> Mid(s2, i, 1) Then

c.Characters(i, 255).Font.Color = vbRed

c(1, 2).Characters(i, 255).Font.Color = vbRed

End If

Next i

Next c

End Sub

=====================================

Will there always be a point where the 2 entries don't match? Are the entries the same length?

Can you post SEVERAL representative samples of the data you're dealing with and let us know what results you expect?

--

Biff

Microsoft Excel MVP

Try the following array formula:

=IF(LEN(B2)=0,IF(LEN(C2)=0,"",LEFT(C2,1)),IF(LEN(C2)=0,LEFT(B2,1),MID(B2,MAX(((MID(B2,ROW(INDIRECT("1:"&MIN(LEN(B2),LEN(C2)))),1)=(MID(C2,ROW(INDIRECT("1:"&MIN(LEN(B2),LEN(C2)))),1)))*ROW(INDIRECT("1:"&MIN(LEN(B2),LEN(C2))))))+1,1)))

This will return the first character from B2 that is different from the corresponding character position in C2. If B2 is empty and C2 is not, the first letter of C2 is returned. If B2 is not empty and C2 is empty, the formula returns the first letter in B2. If both B2 and C2 are empty, the result is an empty string.

This is an array formula, 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 { }. The formula will not work correctly if you do not use CTRL SHIFT ENTER. See www.cpearson.com/Excel/ArrayFormulas.aspx for much more information about array formulas.

Cordially, Chip Pearson Microsoft MVP, Excel Pearson Software Consulting, LLC www.cpearson.com