+ Reply to Thread
Results 1 to 5 of 5

Compare strings and return differences in Excel

  1. #1
    Registered User
    Join Date
    08-09-2020
    Location
    USA
    MS-Off Ver
    2013
    Posts
    2

    Compare strings and return differences in Excel

    Hello,

    I would like to compare two strings within Excel and return any differences. ,

    For example, I would like the following example to return two results: brown, down.

    String 1: A lazy fox jumps over the brown fence. She doesn't jump high enough and falls down.

    String 2: A lazy fox jumps over the fence. She doesn't jump high enough and falls

    Is there a function anyone can share to achieve this? Any help will be greatly appreciated!

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Hi ! Try this demonstration !


    As a VBA beginner starter comparing cells A1 & A2 :

    PHP Code: 
    Sub Demo1()
        
    Split([A1])
        
    Application.Match(VSplit([A2]), 0)
        For 
    R& = 1 To UBound(W):  W(R) = IIf(IsError(W(R)), V(1), False):  Next
        W 
    Filter(WFalseFalse)
        If 
    UBound(W) > -1 Then MsgBox Join(W" , "), vbInformation"Strings differences"
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !

  3. #3
    Forum Contributor
    Join Date
    11-28-2013
    Location
    Ljubljana
    MS-Off Ver
    Office 365
    Posts
    1,054

    Re: Compare strings and return differences in Excel

    here another code (AlphaFrog Coding)
    suppose first string in A1 and second in A2
    the follwing formula
    in A3 =WORDDIF(A1,A2)

    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


    Please Login or Register  to view this content.
    please see my attached example

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Lightbulb Function variation …


    As my demonstration can be easily converted to an user defined function using the cell formula =SDiff(A1,A2) :

    PHP Code: 
    Function SDiff$(S1$, S2$)
             
    Split(S1)
             
    Application.Match(VSplit(S2), 0)
             For 
    R& = 1 To UBound(W):  W(R) = IIf(IsError(W(R)), V(1), False):  Next
             SDiff 
    Join$(Filter(WFalseFalse), " , ")
    End Function 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !

  5. #5
    Registered User
    Join Date
    08-09-2020
    Location
    USA
    MS-Off Ver
    2013
    Posts
    2

    Re: Compare strings and return differences in Excel

    Thank you. Does this function not work if my string has special characters like ( [ / - etc.? My strings are very long, about 5,000 characters and have a ton of special characters. The function is returning some weird results.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Compare large text strings, highlight differences
    By JP Romano in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-06-2018, 09:03 AM
  2. Compare two columns and return differences
    By twachtmann56 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-07-2016, 08:10 PM
  3. Compare character strings in 2 cells and return only the differences
    By Barcenal28 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-09-2015, 05:12 PM
  4. Replies: 1
    Last Post: 03-06-2015, 01:07 PM
  5. Compare two strings and output the differences
    By aliastx in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-14-2013, 10:49 PM
  6. Compare Differences between 2 strings
    By wadafruck in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-11-2012, 07:09 PM
  7. Compare Worksheets and Return Differences
    By bkeller83 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-16-2009, 01:52 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1