I have a description column that I need to identify with a category. I figured out how to bring back a category for a single value, but what if I wanted to determine which of multiple categories? This is how I was able to find 1 value =IF(ISNUMBER(SEARCH("LETTER",D5)),"LETTER", "0") Could someone help me create a formula that would be able to identify and return a value based on a description?

Description Category

PSC 8x10 LETTER - I would like the formula to return LETTER

PSC INSIDE ENVELOPE - I would like the formula to return ENVELOPE

PSC INSERT- I would like the formula to return INSERT

If all you have are those three strings to return, then this should work: =IF(ISNUMBER(SEARCH("LETTER",D5)),"LETTER",

IF(ISNUMBER(SEARCH("ENVELOPE",D5)),"ENVELOPE","INSERT"))In fact, this will also work, and it's a bit shorter:

=IF(ISERROR(FIND("LETTER",D5)),IF(ISERROR(FIND("ENVELOPE",D5)),

"INSERT","ENVELOPE"),"LETTER")Another option is this formula, which will return the last word in any "sentence".

=IFERROR(RIGHT(D5,LEN(D5)-FIND("*",SUBSTITUTE(D5," ","*",LEN(D5)-LEN(SUBSTITUTE(D5," ",""))))),D5)

I can't take create for the last suggestion. The explanation can be found here:

http://www.excel-user.com/2010/10/g...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

When do you think 3D printing will become mainstream?

Discuss in The Lounge

Poll History