1

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.

New contributor
Jonathan Allen Grant is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 1
    there's no function that does this, but you can combine multiple functions into a one liner. – LPChip Aug 14 at 13:51
  • What would that oneliner look like? – Jonathan Allen Grant Aug 14 at 13:56
  • A combination of all the functions necessary. You mentioned you were able to do that. just put one function after another. For example =if(a=b;if(c=d;1;0);2) – LPChip Aug 14 at 15:56
  • But I'd need to check all the modified values in column A against just one value in column B... is that even possible? – Jonathan Allen Grant 2 days ago
  • Can you post some sample data with desired output? Without that it's difficult to understand your question. – Máté Juhász 2 days ago
1

I am supposing in cell A1 and B1 you have:

enter image description here

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: enter image description here

1

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: Check if Column B prefix is present in Column A

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.

Your Answer

Jonathan Allen Grant is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.