Populate new cells based on keywords in description field

Hello, I'm new to the forum so hopefully this problem hasn't come up in the past and already been answered. I looked through and didn't see anything that was exactly what I needed.

I have an ~8000 line spreadsheet with a bunch of information including a description field. I was asked to separate/sort the line items by adding product type and sub-type fields. There are keywords in the existing description field that help me determine which type and sub-type to choose.

For example, the description cell for a specific line item might say something like "Joint Operations Graphic (JOG) 1:250,000 Scale", from this I know the product type is Aeronautical and the sub-type is NGA-JOG.

Is there any way that I can search for/use the keywords and based on these keywords, automatically populate a cell for product type and subtype? I tried doing something like =IF(a2="JOG","Aeronautical") but since all the description fields are different, I think I need something that will search for a specific word within the cell. I also read a little bit about velookup, but that didn't seem to be something that would work for this, but I wouldn't be surprised if I just didn't understand how it works.

Any help would be appreciated!

Great topic, keep myself through looking it.

I have a list of the necessary product types and subtypes, so yes, I have a list of the keywords. Unfortunately some of the description fields don't include a keyword, but only a county name, so I will probably end up doing those manually. I'm not sure I understand the second part of your question, but I currently have the description field sorted alphabetically. I don't need the product type and subtype to be in a specific order after they are populate because I can just sort them alphabetically as well when they are finished. I did however consider the idea of finding a keyword and having excel send that row to a new sheet, and but I have no idea if that's possible or easier. Let me know if I answered your question! Thanks!

Oh, I can see how that could be confusing. The NGA part is really just kind of "jargon". If I can sort by JOG only that is fine, I can find/replace all JOG with NGA-JOG. Unfortunately, no, it will not always be the case that JOG is the 26th character. Let me give you a more comprehensive list of examples.

cos analysis.jpg

Here, column G is the description field. Kenya 250K will need to be sorted to product type Topographic Maps (foreign) and subtype Africa. ONCs will need to be sorted to product type Aeronautical Charts and subtype NGA: ONC (or just ONC, and I can add the NGA later).

For the second example specifically (ONC), if I could run some kind of search that would find the abbreviation ONC and auto populate a subtype column with ONC, then run a search in the subtype column to find ONC and auto populate Aeronautical Charts into a product type column, I think that would be as close to what I need as I can get...