Compare two strings, and find difference?

EtherBoo

New Member
Joined
Oct 26, 2006
Messages
37
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?
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,276
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
 

EtherBoo

New Member
Joined
Oct 26, 2006
Messages
37
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!
 
Last edited:

Niv

New Member
Joined
Jan 25, 2013
Messages
2

ADVERTISEMENT

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.

Thanks in advance
Niv
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,276
Replace this one line...
If WordsA(ndxA) <> vbNullString Then strTemp = strTemp & WordsA(ndxA) & " "

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

Niv

New Member
Joined
Jan 25, 2013
Messages
2

ADVERTISEMENT

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

TwoCowz

New Member
Joined
Dec 18, 2013
Messages
2
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 !
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,276
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?
 

TwoCowz

New Member
Joined
Dec 18, 2013
Messages
2
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,108,940
Messages
5,525,752
Members
409,664
Latest member
translatedotpink

This Week's Hot Topics

Top