For my data set below, I'd like to be able to have a new column where I can see all rows with industry codes that start with "01" for example, and in the new column (cell C1 below) to have "Chemical" as the general industry. Right now I have sub-categories and a lot of times in a graph let's say, I want to analyze all data for the "Chemical" industry in general as opposed to looking at it by sub-categories. I hope my explanation is clear :). Thank you all in advance for your help.
A1 B1 0101 chemical general 0101 chemical general 0102 petrochemical (incl. methanol and syngas) 0102 petrochemical (incl. methanol and syngas) 0201 life sciences general 0201 life sciences general 0202 active pharmaceutical ingredients (API) and additives 0202 active pharmaceutical ingredients (API) and additives
=Left(A2,2) Will return the 01 or 02 digits code that refers to the category here.
Then, in another column, D, you need to use VLookup function to return the name of the category using the code. If you have another standalone table with the list of category codes in column 1 and names in column 2, you can use a formula like this:
C2: is the cell you have with the LEFT function I explained above.
ReferenceTable: The range with the standalone table with the category codes and names.
2: is the column index number for the category names in the table above.
0: is the method of selection and searching which is "Exact match" that suits your data here.
If you do not have or cannot create such a standalone table with category codes and names, this will be hard to extract data from these cells, as we do not know if the category name consists of one word or two, therefore we cannot use a character to separate the category name.