If "A" text is in "A" cell, return "X" text, If "B" text is in "A" Cell return "Y" Text, If "C" Text is in "A" cell, return "Z" Text

I am looking to automate as much of my spreadsheet as possible. In one column I have over 30 variables that are always abbreviated with other additional information and in the next column I want it to say the full word for the abbreviation. For Example:

Column 1      Column 2
xxx-a-xxx     Apple
xxx-b-xxx     Banana
xxx-c-xxx     Cranberry
xxx-d-xxx     Dragon Fruit
xxx-e-xxx     Egg Plant

I want an equation that can check for all my variables and return the correct word in column 2, and as mentioned I have over 30 variables, I want to make sure it checks for -a-,-b-,-c-,-d-,-e-... and whichever one it finds it will it will return the designated word for that abbreviation.

I first tried:

=IF([@Asset]="*-a-*","apple","")

But this did not work at all.
Then googled around and tried:

=IF(ISNUMBER(SEARCH("-a-",A1)),"apple")

But could not find a way to add additional variables.

Is what I'm looking to do even possible?

Thanks in advance.

To answer some questions, creating a new column would defeat the purpose as the information is input into the table as "Building code-unit abbreviation-unit number"

So as another example table will look like

Column 1           Column 2

ABC-EF-001         Exhaust Fan
ABC-EF-002         Exhaust Fan
ABC-FU-001         Furnace
ABC-FU-002         Furnace
ABC-GEN-001        Generator 

Thank you to everyone for your solutions. Very much appreciated!

Create a lookup table:

Then use FILTER:

=FILTER($F$2:$F$6,ISNUMBER(SEARCH($E$2:$E$6,A2)))

Or if one does not have FILTER:

=INDEX($F$2:$F$6,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($E$2:$E$6,A2)),),0))

It's better to list them and use a lookup variation as in Scott's reply, but this could be used:

=CHOOSE(TOCOL(1/ISNUMBER(SEARCH({"-a-","-b-","-c-","-d-","-e-"},A2))*{1,2,3,4,5},3),"Apple","Banana","Cranberry","Dragon Fruit","Egg Plant")

It searches the cell for the array of search strings and multiplies the result with the position of the CHOOSE function to return.

Older version of Excel:
=INDEX({"Apple","Banana","Cranberry","Dragon Fruit","Egg Plant"},AGGREGATE(15,6,{1,2,3,4,5}/(ISNUMBER(SEARCH({"-a-","-b-","-c-","-d-","-e-"},A2))),1))

Edited Post After OP's Updated Query:

As already mentioned above in comments and posted in answer also, using a <kbd>LOOKUP TABLE</kbd> definitely makes it easier. Here is what I came up with, using <kbd>XLOOKUP( )</kbd> with <kbd>TEXTSPLIT( )</kbd> and another alternative version with <kbd>LOOKUP( )</kbd> function for those not using MS365, I hope it works as well.

• Formula used in cell <kbd>B2</kbd>

=XLOOKUP("-"&CHOOSECOLS(TEXTSPLIT(A2,"-"),2)&"-",$E$2:$E$9,$F$2:$F$9)

Or,

• Formula used in cell <kbd>C2</kbd>

=LOOKUP(2,1/SEARCH($E$2:$E$9,A2),$F$2:$F$9)

Or,

• Formula used in cell <kbd>B2</kbd> --> For case-sensitive.

=LOOKUP(2,1/FIND($E$2:$E$9,A2),$F$2:$F$9)

Another way using <kbd>XLOOKUP( )</kbd> with <kbd>TEXTBEFORE( )</kbd> & <kbd>TEXTAFTER( )</kbd>

• Formula used in cell <kbd>B2</kbd>

=XLOOKUP("-"&TEXTBEFORE(TEXTAFTER(A2:A11,"-"),"-")&"-",E2:E9,F2:F9)

=XLOOKUP(INDEX(TEXTSPLIT([@[Column 1]],"-"),2),Table5[Code],Table5[Fruit],"")

If the data in Column 1 uses a consistent format (each code is in the same position and you're using a "-" as a separator), you can use this same formula for any code by simply changing the value in the INDEX formula (currently "2") and pointing your XLOOKUP to the appropriate table and columns.

I used the following for a slightly different application:

=IF(FIND("a",A19,1)>0,1,0)

So find() returns a number showing the position the character is found, you might need to wrap find() in iferror() though.

After that you can go to a lookup table or similar.