Searching cells to see if it contains multiple text strings

[Dannytt93]

Hello,

I am trying to search a cell to see if it contains an abbreviation for job title.. if it contains the abbreviation, i want it to put in the full job title which is reference in another cell.

I have got it working for one text cell by the following:

=IF(ISNUMBER(SEARCH("Abbreviation1",$A5)),Full title cell1)

I want to run the formula so that if it contains abbreviation2, it will insert full title cell2 and so on....

I tried this:

=IF(AND(ISNUMBER(SEARCH("abbreviation1,$A5)),Full Title cell1), ((Abbreviation2,$A5),Full title cell 2)

but it doesn't work.

Any help anyone is able to provide would be greatly appreciated..

Kind Regards

 

[Joe4]

Are you looking for the cell to contain BOTH of the abbreviations, or just either one?
How many different values will you be searching for?
Is a VBA solution acceptable?

 

[Richard U]

I don't think you want the and in there, instead a nested if would work better, try:

=IF(ISNUMBER(SEARCH("abbreviation1",$A5)),Full Title cell1,IF(ISNUMBER(SEARCH(abbreviation2,$A5)),"Full Title cell2",somedefaultvalue))

 

[Joe4]

I don't think you want the and in there, instead a nested if would work better, try:

Yes, that is why I questioned if they are looking for both words contained in a single cell (that is the only time it would make sense to try to incorporate "AND").

The solution Richard posted should work if there are only two options, and you could extend it out similarly for a few more options. However, if you have a lot of options, that formula is going to get to be quite long and cumbersome, and that is where you might want to look at an alternative solution like VBA.

 

[Toadstool]

I'm reading the requirement as having several possible abbreviations so I think a list of abbreviation/full title may be necessary.

Here I'm using SUBSTITUTE to make the change. You do have to be careful the abbreviation may not be a part of another word (as in row 6).

	B	C	D	E	F	G
Original	SUBSTITUTE	Abbreviation	Full
Charlie Prof. of Economics	Charlie Professor of Economics	Prof.	Professor
Bert the Binman	Bert the Refuse Disposal Executive	Prof	Professor
John is PA to the CEO	John is Personal Assistant to the CEO	Ftr	Diesel Fitter
Alf is professional	Alf is professional	Binman	Refuse Disposal Executive
Alf is Professional	Alf is Professoressional	PA	Personal Assistant
	Cowboy	Bovine Management Consultant

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/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] "]C2
[/TH]
[TD="align: left"]=IFERROR(SUBSTITUTE(B2,INDEX($F$2:$F$99,AGGREGATE(15,6,ROW($F$2:$F$99)-ROW($F$1)/(FIND($F$2:$F$99,B2)),1)),INDEX($G$2:$G$99,AGGREGATE(15,6,ROW($F$2:$F$99)-ROW($F$1)/(FIND($F$2:$F$99,B2)),1))),B2&"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]