formula to compare two strings

[pwl2706]

Hi,

we have a bunch of strings comprising of characters "A","B"..."Z" (and no others). A typical string looks like <CODE>ABZYC</CODE>. The strings are given in pairs like <CODE>ABC</CODE>, <CODE>ABDC</CODE>.

The strings are match if one string is contained in the other (i.e either one of the two strings contain all the alphabets of the other). The order in which the string appears don't matter.

[FONT=Courier New]ACBD[/FONT],<CODE>AC</CODE> - <CODE>Match</CODE>
<CODE>ACBD</CODE>,<CODE>CA</CODE> - <CODE>Match</CODE>
<CODE>ACBD</CODE>,<CODE>ADB</CODE> - <CODE>Match</CODE>
<CODE>AC</CODE>,<CODE>ABCD</CODE> - <CODE>Match</CODE>
<CODE>ABC</CODE>, <CODE>ABD</CODE> - <CODE>No Match</CODE>

so, is there a formula way of doing that, or is it VBA only?

thanks
Philip

PS - I did something in VBA, but wondered if there was a lovely formula solution?

 

[schielrn]

Welcome to the Board!

This would work, but note the last scenario. I don't know what you would want for that, but I assume No Match, but maybe you cannot have a scenario like that?

Excel Workbook
	A	B	C
1	ACDB	AC	Match
2	ACDB	CA	Match
3	ACDB	ABCD	Match
4	ACDB	ABCD	Match
5	ACBC	ABD	No Match
6	A	AAA	Match
Sheet2

#VALUE!

 

[schielrn]

Note that what I have posted are array formulas and would need to be confirmed with control+shift+enter.

 

[pwl2706]

WOW, thanks so much for that!

(...copy pastes answer to StackOverFlow to show off and get reputation Compare strings in excel vba - Stack Overflow!)

 

[schielrn]

Actually I noticed that my values didn't match exactly to the table you provided, here is an updated formula that looks both ways:

Excel Workbook
	A	B	C
1	ACDB	AC	Match
2	ACDB	CA	Match
3	ACDB	ABD	Match
4	AC	ABCD	Match
5	ACBC	ABD	No Match
6	A	AAA	Match
Sheet2

#VALUE!