Cell contain multiple text

[Shahuru]

Hi I need assistance to build a formula for the below scenario,

BB,DN = HB

BB,DN,LN = FB

If cell A1 contains " BB,DN " I want the result to go B1 as " HB" and if cell A1 contains " BB,DN,LN" results to be in B1 as "FB"


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]BB,DN,LN,
[/TD]
[TD]FB
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BB,DN
[/TD]
[TD]HB
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[ACommandLineKindaGuy]

There's a lot of ways to do this. And your question is open to some interpretation, so I answered it for a couple of interpretations. There are other tests that may be needed, such as is "bB,dn" also equal to "HB" and so on. If so, post back.

I can't post spreadsheet for some reason, so this gets clunky:

A1: BB,DN
B1: HB
A2: BB,DN,LN
B2: FB

A4: BB,DN,LN
If you have a lot of data and the text is an exact match
B4: =IF(ISNA(VLOOKUP(A4,$A$1:$B$2,1,FALSE)),"Not found",VLOOKUP(A4,$A$1:$B$2,2,FALSE))

A5: abcBB,DNxyz
If text is within what you are examining
B5: =IF(OR(ISERR(FIND(A1,A5)),ISERR(FIND(A2,A5))),"Not found",IF(FIND(A1,A5),B1,IF(FIND(A2,A5),B2,"Not found")))

 

[Fluff]

How about

Book1
	A	B
1	BB,Dn,Ln,	FB
2	bb,DN	HB
3	aa,BB,DN	HB
4	aa,bb,dn,LJ	HB
5	aa,bb,dn,ln,xx	FB
Sheet2

Cell Formulas
Range	Formula
B1	=IF(ISNUMBER(SEARCH("BB,DN,LN",A1)),"FB",IF(ISNUMBER(SEARCH("BB,DN",A1)),"HB",""))

 

[Fluff]

@ACommandLineKindaGuy
Your B5 formula will return "not found", even if A5 equals either A1 or A2. Even if you make that change if A5=A2 then the formula will return #VALUE!

 

[salim hasan]

Suppose your date in column(a),starting with A1
Try this formula

=IF(A1="","",IFERROR(INDEX({"HB";"FB"},CHOOSE(SUM(IF(A1="BB,DN",1,0),IF(A1="BB,DN,LN",2,0)),1,2)),"No Match"))

	A	B
BB,DN	HB
BB,DN,LN	FB
BB,DN,LN,145	No Match

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=IF(A1="","",IFERROR(INDEX({"HB";"FB"},CHOOSE(SUM(IF(A1="BB,DN",1,0),IF(A1="BB,DN,LN",2,0)),1,2)),"No Match"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

You could slim that formula down slightly like
=IF(A1="","",IFERROR(INDEX({"HB";"FB"},IF(A1="BB,DN",1,IF(A1="BB,DN,LN",2,""))),"No Match"))

 

[ACommandLineKindaGuy]

How weird--It worked in the worksheet I had to transcribe once I saw I had no privilege to upload files...

 

[Shahuru]

I wanted the result to be ; if the cell contains, DN BB the result should get HB , eg if in Cell A1 has BB TR,GT,LN,KR,DN, - as DN and BB is in the cell i would like only search for those two and result to HB, in most case other character will be there in the cell. hence just wanted to search for specific text and the results to be based as follow.

DN LN BB ( may not be in same pattern) - result to FB
DN BB ( may not be in same pattern) - result to HB
DN WN LQ BR LN BB ( may not be in same pattern) - result to AI

Thanks for your help.

 

[Fluff]

For future reference it's always best to give an accurate description of your data & your needs in the original post, to save members wasting their time providing something that won't work.

That said can you please do that now? Thanks

 

[Shahuru]

My apologies for not described it in my initial post; I have list of data contains multiple texts in each cell, out of it i wanted to take few texts and results to be as follow examples.

Cell A1 - DN, BB , TK, GR
Cell A2 - BB,TG,LN,UPY, BR , LQ
Cell A3 - AH, LN, WN,BR ,BB , LQ,DN
Cell A4- BB, DN , TK, GR

If DN and BB found Result to be HB
IF DN BB LN found results to be FB
if LN DN BB WN LQ BR found results to be AI

Thanks for the support.