Finding the character where two strings do not match

Hello,

=LOOKUP(2,1/(1=FIND(LEFT(" "&A2&REPT("|",1+LEN(A1)),ROW(INDIRECT("1:"&2+LEN(A2)))),

" "&A1&REPT("#",1+LEN(A2)))),ROW(INDIRECT("1:"&2+LEN(A2))))

"|" and "#" should not appear in your input.

Regards,

Bernd

PS: I do not like worksheet functions of this length :-)

---

www.sulprobil.com

You mean FORMULAS, right? <g>

Programmers always want to reinvent the wheel.

We're seeing you a lot in these forums, Bernd. Why didn't you post this much in the ng's?

On a side note: while I disagree with your reasoning for not wanting to use Morefunc at all (open source), I am starting to come around to the idea of maybe not using it in Excel 2007 as it seems there are some "bugs" when using it in Excel 2007.

--

Biff

Microsoft Excel MVP

I've noticed that I'm getting unexpected results.

The formula is returning some strange results:

Text 1             Text 2            Formula Result

aaaaaa           aaaaab           6  (This is correct)

blah                blax               4  (this is also correct)

apple              bpple              6

aaaaaaaaab    aaaaaaaaac    10

aaaaaa           aabbab           6

Now:

If copy the formula to the next line, I get:

Text 1             Text 2            Formula Result

aaaaaa           aaaaab           2

Once I go into the 2nd formula's cell and do a ctrl-shift-enter, the result is 5.

Can array-entered functions be copy/pasteed?

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

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