How to do a partial string comparison in Excel?

Question

I have two columns in Excel. Both columns have a list of string that are suffixed with a random character. The format of the prefix is two strings appended by an underscore, and the suffix is added also with an underscore.

i.e.

ABC_DEF_a => (prefix = "ABC_DEF", suffix = "a") HJDSGDJ_KJ1_a10 => (prefix = "HJDSGDJ_KJ1", suffix = "a10"

How do I compare if the prefix in Column B is in Column A?

Edit: I know I can do this by splitting the string into three sections, and then combining the first two sections into one column, and then checking if the column is in the other with a VLOOKUP. But I was looking for a oneliner function.

Answer 1

I am supposing in cell A1 and B1 you have:

And in column C there is a TRUE(VERDADEIRO) or FALSE(FALSO) that returns if the prefix is equal or not. (sorry for the picture in portuguese)

The formula I came up with is:

=EXACT(LEFT(SUBSTITUTE(A1;"_";"\";2);SEARCH("\";SUBSTITUTE(A1;"_";"\";2))-1);LEFT(SUBSTITUTE(B1;"_";"\";2);SEARCH("\";SUBSTITUTE(B1;"_";"\";2))-1))

---

Explanation:

=EXACT(str1,str2)

It compares if strings str1 and str2 are equal or not. We must extract the preffix from columns A and B. To do that, we use:

=LEFT(text, [num_chars])

It extracts [num_chars] characters from the string text. To know how much characters we must extract, we substitute the second underscore _ with a dummy character \:

=SUBSTITUTE(text, old_text, new_text, [instance])
=SUBSTITUTE(A1;"_";"\";2)

The trick here is the optional argument [instance]. We set it to 2, to substitute the second ocurrence of the underscore character.

=SEARCH("\";SUBSTITUTE(A1;"_";"\";2))-1)

With this formula, we find the position of the dummy character \. Putting it all together, we get the formula above.

In steps, if we brake down the formula, we have:

Answer 2

I read your question as wanting to know for each string in Column B, if its prefix is found anywhere in Column A. So this answer solves that problem.

I couldn't get it in one line (because it has to look everywhere in Column A). But it only requires one extra column.

First, a formula to extract the prefix out of any string:

=LEFT(Column_A, FIND(Delimiter, Column_A, 1 + FIND(Delimiter, Column_A)) - 1)

• Delimiter is the "_" character
• The inner FIND() call finds the location of the first underscore. Then it uses that as a starting point to FIND the location of the second underscore.
• The LEFT() function returns a number of characters starting from the left of the string. So we subtract 1 from the location of the second underscore, and get the prefix of the string.

So you use this to get a list of every Column A prefix. Then you use an array formula to check if each prefix in Column B is present in the list of Column A prefixes.

{=OR(LEFT(Column_B, FIND(Delimiter, Column_B, 1 + FIND(Delimiter, Column_B)) - 1)=Prefix_A)}

• Prefix_A is the entire list of Column A prefixes.
• This is the same formula for extracting prefixes as was applied to Column A
• This needs to be an array formula because the statement inside the OR() function returns an array of TRUE and FALSE values

Here is a screenshot of the spreadsheet I built to answer this:

I used FormulaChop to generate these example formulas (full disclosure: I wrote FormulaChop). Here is a screenshot of the FormulaChop output for the first formula. Here is a link to the spreadsheet I built to answer this question.