Ever had the need to compare two sentences regardless of the order of words? If so, I’ve got you covered! In this article, I’ll show you a clever Google Sheets formula that allows you to compare two strings irrespective of the word positions. Trust me, this formula will come in handy!
The Challenge of Comparing Two Strings with Different Word Positions
Comparing two strings is easy when the word positions are the same. All you need is a simple Google Sheets formula to test it out. For example, if cell A1 and B1 both contain the phrase “Brazil and Canada,” you can use the formula =A1=B1 in cell C1. This formula will return TRUE since both values match.
But what if cell B1 contains “Canada and Brazil”? Unfortunately, the above formula would return FALSE. Although both strings are the same when compared word by word, the word sequence is different. That’s why the formula fails to recognize them as a match.
Introducing a Formula to Compare Strings Irrespective of Word Positions
To overcome this challenge, I’ve developed a formula that compares two strings irrespective of the word positions in Google Sheets. This formula utilizes a combination of the SPLIT and SEARCH functions to accomplish the task seamlessly.
Let me explain how to use this formula step by step.
Google Sheets Formula to Compare Two Strings Irrespective of Word Positions
To demonstrate how to code this formula, let’s use the sample data provided in the screenshot below.
The formula will be written in cell D1 in the same sheet. I’ll start with a non-array formula to simplify the explanation, but I’ll also provide an array formula at the end of this tutorial for more advanced users.
Non-Array Formula that Compares Two Strings Ignoring Word Sequence
Step 1:
In this step, I’ll split the string in cell C1 using the SPLIT function. This will return the individual words as separate strings.
=split(C1," ")Step 2:
Next, I’ll check if these words are present in cell B1 using the SEARCH function. Since we’re checking multiple strings, we need to use the ArrayFormula function in combination with SEARCH.
=ArrayFormula(search(split(C1," "),B1))This formula will return three numeric values representing the position of each word in cell B1. If there is any mismatch, the formula will return a #VALUE! error.
Based on this output, we can infer that if there are no errors in the formula output, it means that all the words in cell C1 exist in cell B1.
Step 3:
Now, let’s perform the same testing with the string in cell B1.
=ArrayFormula(search(split(B1," "),C1))This formula checks if all the words in cell B1 are present in cell C1.
Step 4:
Combining Steps 2 and 3, we get the following formula:
=ArrayFormula(IFERROR({search(split(C1," "),B1),search(split(B1," "),C1)},1000))This formula compares two strings irrespective of the word positions in Google Sheets. If there is a mismatch, the formula will return at least one #VALUE! error.
Step 5:
To get a more user-friendly result, we can use the IFERROR function to replace the #VALUE! errors with the value of our choice. In this case, I’ll replace the errors with the value 1000.
=if(sum(ArrayFormula(IFERROR({search(split(C1," "),B1),search(split(B1," "),C1)},1000)))>=100,FALSE,TRUE)This final formula will return TRUE if the two strings match and FALSE if there is a mismatch. The IFERROR function helps us achieve this outcome.
Array Formula that Compares Two Strings Ignoring Word Sequence
For more advanced users, I’ve converted the above formula into an array formula. You can simply enter it in cell D1 and let it expand automatically.
=ArrayFormula(if(len(B1:B),(IF(MMULT(IFERROR(IF(LEN(B1:B),{search(split(C1:C," "),B1:B),search(split(B1:B," "),C1:C)},0),10000),SIGN(ROW(INDIRECT("A1:A"&COLUMNS(ArrayFormula(IFERROR(IF(LEN(B1:B),{search(split(C1:C," "),B1:B),search(split(B1:B," "),C1:C)},),99999)))))))>=10000,FALSE,TRUE)),))This formula follows the same logic as the non-array formula, but it accounts for multiple rows. Instead of using the SUM function, it utilizes the MMULT function to sum values in each row.
I won’t go into further explanation of this formula, but feel free to use it as is. Just make sure to adjust the column references (C1:C and B1:B) to match the columns containing your strings to compare. Keep A1:A as is.
That’s all there is to it! Now you have a powerful formula to compare two strings irrespective of the word positions in Google Sheets. Say goodbye to frustrations caused by differing word sequences and embrace the simplicity of this solution.
If you want to explore more Google Sheets tips and tricks, visit Crawlan.com. I’m always sharing my juiciest secrets with my besties over there!
