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?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,449
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,449
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,449
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).
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,987
Members
430,100
Latest member
namhnz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top