# Thread: Compare two strings, and find difference?

1. ## Compare two strings, and find difference?

This may not be possible with excel, but this is what I'm trying to do

A1 = Hello John, my name is Steve, how was your day?
B1 = Hello John, my name is Steve, was day?

I need C1 to return:
how your

Is this possible?

2. ## Re: Compare two strings, and find difference?

Here's a custom function called WORDDIF that may do what you want.

To install the custom function...
• Alt+F11 to open the VBA Editor
• From the VBA menu, select Insert\ Module
• Paste the code below in the VBA Edit window

Back in Excel, put this formula in C1
=WORDDIF(A1,B1)
Code:
```Function WORDDIF(rngA As Range, rngB As Range) As String

Dim WordsA As Variant, WordsB As Variant
Dim ndxA As Long, ndxB As Long, strTemp As String

WordsA = Split(rngA.Text, " ")
WordsB = Split(rngB.Text, " ")

For ndxB = LBound(WordsB) To UBound(WordsB)
For ndxA = LBound(WordsA) To UBound(WordsA)
If StrComp(WordsA(ndxA), WordsB(ndxB), vbTextCompare) = 0 Then
WordsA(ndxA) = vbNullString
Exit For
End If
Next ndxA
Next ndxB

For ndxA = LBound(WordsA) To UBound(WordsA)
If WordsA(ndxA) <> vbNullString Then strTemp = strTemp & WordsA(ndxA) & " "
Next ndxA

WORDDIF = Trim(strTemp)

End Function```

AlphaFrog,

Nice!

4. ## Re: Compare two strings, and find difference?

WOW. Thank you 100 times over!

Now, I'm not sure if this is possible, but is it possible to do something where it will do something like this

A1: Jim-Steve-Paul-Phil
B1: Steve-Phil
C1: Jim--Phil

I'm going to make some edits to the original, concat function that made this, so I might not need the change, but it would still be a big help if possible!

Thank you so much again!

Cancel that request, I changed the concat function used to make these giant strings to have spaces, and now it's giving me exactly what I need.

Thank you SO much again!

5. ## Re: Compare two strings, and find difference?

Hello folks

I'm sorry for digging this old post out but, I need some help with that function in post 2. Original function works like a charm but can some helpful soul help me to modify it to do something like this

Cell 1: 1 2 3 4 5 6 7 8
Cell 2: 1 2 3 5 6 7 8
Outcome: - - - 4 - - - -

Replace same word with - and still show words that are different. All strings are 8 words long if it's relevant.

Niv

6. ## Re: Compare two strings, and find difference?

Replace this one line...
If WordsA(ndxA) <> vbNullString Then strTemp = strTemp & WordsA(ndxA) & " "

With this...
strTemp = strTemp & IIf(WordsA(ndxA) <> vbNullString, WordsA(ndxA), "-") & " "

7. ## Re: Compare two strings, and find difference?

Works perfect, you are an absolute star AlphaFrog. Thank You very much.

8. ## Re: Compare two strings, and find difference?

AlphaFrog,

Sorry for resurrecting the old post!

First off, thank you for the amazing formula, its great to see coding in VBA at its best.

I have two strings which I compare nutritional values and they are all text lots of numbers and letters ,when I implemented your function, I receive a blank. What I am assuming is, somebody entered an extra space somewhere.

Is there a way to insert some code to identify which character # (slot) the space firstly "changes" per say ? I don't think this would be a hard addition you would just return the number you identify with the "change" as numerical value, I think there's a function for it !

Cheers !

9. ## Re: Compare two strings, and find difference?

Originally Posted by TwoCowz
Is there a way to insert some code to identify which character # (slot) the space firstly "changes" per say ? I don't think this would be a hard addition you would just return the number you identify with the "change" as numerical value

I don't really follow what you're asking. Could you give some data examples the expected result like in the original question?

10. ## Re: Compare two strings, and find difference?

When I have the two cells comparing

john jacob juinor
john jacob salt

I enter the A1 content as 1st string of words above, and the second one, the result is "junior" so it identified which value changed !

I want excel to return the character that is the letter "J" in the word junior. 11 to be exact purely for identification purposes as if I have a paragraph of ingredients I can spot where the recipe calls for sugar (packet) , sugar (cane).

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•