Compare two or more text strings in Excel
If you want to compare two or more text strings in a worksheet with case sensitive or not case sensitive as following screenshot shown, this article, I will talk about some useful formulas for you to deal with this task in Excel.
- Compare two or more text strings with case sensitive
- Compare two or more text strings with case insensitive
Compare two or more text strings with case sensitive
Compare two text strings with case sensitive:
Normally, the EXACT function can help you to compare if the two or more strings are exactly equal, to compare only two text strings, the generic syntax is:
- text1: The first text string you want to compare.
- text2: The second text string is used to compare with first text string.
1. Copy or enter the below formula into a blank cell:
2. And then, drag the fill handle down to the cells you want to apply this formula, and you will get TRUE if the two strings are equal, FALSE if not equal. See screenshot:
Tips: If you would like to use a specific text to replace the default TRUE or FALSE, you can combine the IF function with the EXACT function, please apply the following formula:
Compare multiple text strings with case sensitive:
Sometimes, you may need to compare multiple text strings, in this case, you should combine the AND function with the EXACT function, the generic syntax is:
- range: The range of cells that you want to compare;
- value: The text or cell in the range of cells is used to compare.
1. Please enter or copy the below formula into a blank cell:
2. Then, press Ctrl + Shift + Enter keys together to get the correct result, and then, copy and fill this formula to other cells that you want to use, and you will get the result as below screenshot shown:
Tips: To display other specific text in the result instead of the TRUE or FALSE, please apply the following formula:
Please remember to press Ctrl + Shift + Enter keys together to get the correct result.
Compare two or more text strings with case insensitive
If you want to get the result without case sensitive when comparing two or more text strings, please use the below formulas:
Compare two text strings without case sensitive:
1. Enter or copy the below simple formula into a blank cell where you want to get the result:
2. And then, you will get the result as below screenshot shown:
Tips: To output your own texts for equals and differences, please use this formula:
Compare multiple text strings without case sensitive:
1. To check if multiple text strings are equal without case sensitive, please use the following formula:
Note: In the above formula, A2:D2 is the range of cells that you want to compare, A2 is the cell value in your data range which used to compare, and the number 4 refers to the number of cells that you want to check.
2. Then, drag the fill handle down to the cells to apply this formula, and the result will be displayed as below screenshot shown:
Tips: Use the below formula to get the specific text that you want to display in the result:
Relative functions used:
- EXACT:
- The EXACT function compares two strings and returns TRUE if they are exactly same, or returns FALSE.
- IF:
- It performs a simple logical test which depending on the comparison result, and it returns one value if a result is TRUE, or another value if result is FALSE.
- COUNTIF:
- The COUNTIF function is a statistical function in Excel which is used to count the number of cells that meet a criterion.
More articles:
- Check if cell equals any value in list
- For example, I have a list of text strings in column A, and now, I want to check if cell value in this column equals any value in another list of column E. If equals, display TRUE, otherwise, a FALSE value is displayed.
- If cell contains text then display in Excel
- If you have a list of text strings in column A, and a row of keywords, now, you need to check if the keywords are appear in the text string. If the keywords appear in the cell, displaying it, if not, blank cell is displayed.
- Show specific text based on value in Excel
- Supposing, you have a list of values, now, you would like to show a specific text based on the value. For example, if cell value is greater than 1000, then “Increase” text is displayed in another column as below screenshot shown. To deal with this task, both the REPT and IF functions can do you a favor.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.
- Designed for 1500 work scenarios, helps you solve 80% Excel problems.
- Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
- Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
- 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.
