Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > Excel String Compare – 5 Easy Methods

Excel String Compare – 5 Easy Methods

(Note: This guide on Excel string compare is suitable for all Excel versions including Office 365)

Excel is a very reliable spreadsheet tool. In spite of this, errors can creep in due to human oversight. And some of these errors can make or break your database. 

Let us say, for example, you have a list of blacklisted contacts. Now, somebody from your sales team accidentally mixed up the list with good leads. What do you do now? 

While you can technically check the names one by one, it is practically impossible to compare hundreds of names manually. That’s why we need a fast but reliable way to compare strings in Excel. 

Fortunately, Excel has built-in features and functions that can easily compare strings. In this guide, let us explore them. 

You’ll learn:

  1. Method 1: How to Compare Two Case Insensitive Strings? 
  2. Method 2: How to Compare Two Case Sensitive Strings?
  3. Method 3: How to Compare Two Strings Using VBA? 
  4. Method 4: How to Compare Strings Using Conditional Formatting?
  5. Method 5: How to Compare Strings for Similarity?

Related:

How to Remove Spaces in Excel? 3 Easy Methods

How to Insert Bullet Points in Excel? 5 Easy Methods

How to Set Print Area in Excel? Step-by-Step Guide

Method 1: How to Compare Two Case Insensitive Strings? 

To compare two strings, you can simply use the formula IF(A2=B2, “Equal”, “Error”) and drag it to the rest of the cells, provided you don’t care about the cases. 

Excel string compare using the IF function
Excel string compare using the IF function

If necessary, you can later filter similar strings and delete them. Please note that this “IF” function compares strings, numbers, dates, boolean expressions, etc. 

Method 2: How to Compare Two Case Sensitive Strings?

In case, you want to compare strings while accounting for different upper and lower case versions use the EXACT function. 

Let us see how to do this: 

  1. In a separate column, enter the formula IF(EXACT(D2,E2), “Equal”, “Error”). 
  1. This will return “Equal” if the values are exactly the same. Drag the formula to the entire range. 
Case sensitive Excel string compare using IF and EXACT functions
Case sensitive Excel string compare using IF and EXACT functions


Please note that the IF formula is optional and is only used for displaying a message instead of “TRUE” or “FALSE”. 

Also Read:

How to VLOOKUP From Another Sheet?

How to Move Rows in Excel? 5 Easy Methods

How to Fix the Excel Circular Reference Error?

Method 3: How to Compare Two Strings Using VBA? 

Well, the methods discussed above work only if you are comparing simple strings. What if you need to compare complex strings? 

Isn’t there any efficient way to compare strings in Excel? 

Fortunately, we have VBA. Using this VBA code, we can not only compare two strings but also highlight the differences between them. 

Let us see how to do this: 

  1. Open the relevant Excel spreadsheet and press Alt+F11 to go to the VBA editor. 
  1. Here, click on the Module option under Insert 
Click on the Module option under the Insert menu
Click on the Module option under the Insert menu
  1. In the New Module window, paste the following VBA code and run it using the F5 shortcut. 
Sub highlight()
    Dim R1 As Range
    Dim R2 As Range
    Dim Txt As String
    Dim C1 As Range
    Dim C2 As Range
    Dim I As Long
    Dim J As Integer
    Dim xL As Integer
    Dim xD As Boolean
    On Error Resume Next
    If ActiveWindow.RangeSelection.Count > 1 Then
      Txt = ActiveWindow.RangeSelection.AddressLocal
    Else
      Txt = ActiveSheet.UsedRange.AddressLocal
    End If
lOne:
    Set R1 = Application.InputBox("Range A:", "Compare_Strings", Txt, , , , , 8)
    If R1 Is Nothing Then Exit Sub
    If R1.Columns.Count > 1 Or R1.Areas.Count > 1 Then
        MsgBox "Multiple ranges or columns have been selected ", vbInformation, "Compare_Stringsl"
        GoTo lOne
    End If
lTwo:
    Set R2 = Application.InputBox("Range B:", "Compare_Strings", "", , , , , 8)
    If R2 Is Nothing Then Exit Sub
    If R2.Columns.Count > 1 Or R2.Areas.Count > 1 Then
        MsgBox "Multiple ranges or columns have been selected ", vbInformation, "Compare_Strings"
        GoTo lTwo
    End If
    If R1.CountLarge <> R2.CountLarge Then
       MsgBox "Two selected ranges must have the same numbers of cells ", vbInformation, "Compare_Strings"
       GoTo lTwo
    End If
    xD = (MsgBox("Yes to Highlight Common String, No to highlight differences ", vbYesNo + vbQuestion, "Compare_Strings") = vbNo)
    Application.ScreenUpdating = False
    R2.Font.ColorIndex = xlAutomatic
    For I = 1 To R1.Count
        Set C1 = R1.Cells(I)
        Set C2 = R2.Cells(I)
        If C1.Value2 = C2.Value2 Then
            If Not xD Then C2.Font.Color = vbGreen
        Else
            xL = Len(C1.Value2)
            For J = 1 To xL
                If Not C1.Characters(J, 1).Text = C2.Characters(J, 1).Text Then Exit For
            Next J
            If Not xD Then
                If J <= Len(C2.Value2) And J > 1 Then
                    C2.Characters(1, J - 1).Font.Color = vbGreen
                End If
            Else
                If J <= Len(C2.Value2) Then
                    C2.Characters(J, Len(C2.Value2) - J + 1).Font.Color = vbRed
                End If
            End If
        End If
    Next
    Application.ScreenUpdating = True
End Sub
  1. In the Compare_Strings dialog box, select the ranges of the strings you want to compare and click OK. 
Select Range A
Select Range A
Select Range B
Select Range B
  1. Choose whether to highlight the differences or the similarities between the strings.  All differences will be highlighted in red and all similarities will be highlighted in green. 

Method 4: How to Compare Strings Using Conditional Formatting?

What if you are in a hurry and want to quickly compare strings for duplicates?

Well, you can take the help of the Conditional Formatting feature to instantly compare and highlight duplicates in Excel. 

To do this, just select the range of cells where you want to look for duplicates and go to Conditional Formatting > Highlight Cell Rules > Duplicate Values. This will highlight all duplicates in this range. 

Highlight duplicates using Conditional Formatting
Highlight duplicates using Conditional Formatting
Equal strings will be highlighted in red
Equal strings will be highlighted in red

Method 5: How to Compare Strings for Similarity? 

If you are looking to find similarities between two sets of un-identical strings, you can use this method. 

Just enter the formula IFERROR( IF (SEARCH (text1, text2),if_true),if_false) in an adjacent column and drag it to the entire range. In this example, we are comparing column D with column E. Hence the formula will be =IFERROR(IF(SEARCH(D2,E2),”Similar”),”Different”)

Using IFERROR to find similar strings
Using IFERROR to find similar strings

This will check if text1 is inside text2 or not and return “Similar” or “Different” accordingly. 

Suggested Reads:

How to Extract an Excel Substring? – 6 Best Methods

Excel Quick Analysis Tool – The Best Guide (5 Examples)

How to Lock Cells in Excel?— 4 Best Methods with Examples

Let’s Wrap Up

In this short guide, we discussed five easy methods to compare strings in Excel. Try each one out and use the one which suits you best. In fact, you can modify some of these methods to your liking. Please let me know if you have any questions in the comments section. 

We are always happy to help. 

Please visit our free resources section for more high-quality Excel guides.

Ready to upskill your Excel knowledge? Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 130+ training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

How to Set Up Customers and Jobs in QuickBooks Pro 2017 Part 1

Using Reports with Microsoft Project 2013

How to Count Characters in Excel? 4 Different Scenarios

QuickBooks 2020 – Section 2 Transcript

How to Curve Text in Word? 2 Easy Ways

Using the Mail Merge Wizard in Word 2010

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)