Profiling off of a document path

I am trying to profile about 100,000 documents based off of their file paths. I want to be able to tell the document type and document sub type just based on the document path. For example Y:\Clients\J\Jamison, Andrew\Tax\1040.Tax - Jamison, Andrew - 2010 - Revision.Pdf the document type would be Taxes and the document sub type would be W2. I am trying to profile about 100,000 documents so I don't really want to do this by hand. I am looking for an excel function that I might have missed or possibly a formula that would help me out. Any help is greatly appreciated. Thanks

• what are the various "document types" & "sub-types"?
• what are the conditions based on which "document type" and "sub-type" can be identified?
• what would the final result to look like (create a small sample, say 15-20 rows of data & share it)

I've attached a new workbook where on the second sheet it has all of the doc types and doc sub types. The conditions on what it can be based are found in the file path.

There is no second sheet in the attachment.
Also, try to keep the file size low by sending a sample of your problem (say 20-25 entries only). If a solution works for 20 rows, in most cases, it will work for all rows.