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

Question

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!

Answer 1

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))