Page 1 of 8 123 ... LastLast
Results 1 to 10 of 71

Thread: Compare two strings, and find difference?

  1. #1
    New Member
    Join Date
    Oct 2006
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,828
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    5 Thread(s)

    Default 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

  3. #3
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,540
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Compare two strings, and find difference?

    AlphaFrog,

    Nice!
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  4. #4
    New Member
    Join Date
    Oct 2006
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!
    Last edited by EtherBoo; Aug 7th, 2010 at 12:26 AM.

  5. #5
    New Member
    Join Date
    Jan 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

    Thanks in advance
    Niv

  6. #6
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,828
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    5 Thread(s)

    Default 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), "-") & " "
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  7. #7
    New Member
    Join Date
    Jan 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Compare two strings, and find difference?

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

  8. #8
    New Member
    Join Date
    Dec 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #9
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,828
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Compare two strings, and find difference?

    Quote Originally Posted by TwoCowz View Post
    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?
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  10. #10
    New Member
    Join Date
    Dec 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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).

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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