Forum Discussion

lucahla's avatar
lucahla
Copper Contributor
Jun 10, 2020
Solved

Categorizing in Excel

Hi! I want to categorize specific data in Excel and am looking for a function/formula to do so.    I have a column "C", where there are indeces that end in a specific text snippet, similar to this...
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jun 11, 2020

    lucahla Sorry to interrupt, but have you had a chance to look at the file I sent earlier? It contains a list where you can enter the snippets you indexed. The formula (in B2 and copied down)

    =IFERROR(MATCH(TRUE,(ISNUMBER(SEARCH($D$2:$D$7,A2,1))),0),"")

    will give you the position in the list when a snippet is found in the text in A. Just amend to cell references and list range to fit your real worksheet.

     

    Now, you added another dimension to your question by stating that the snippets are always in the last two positions of the text. In that case you could expand the reference to A2 in the above formula to become RIGHT(A2,2) . This will avoid incorrect indexing in case your text would be like "ABC3456-78_9 DE" where it would pickup the BC as the snippet, assuming your snippet-list is alphabetically sorted. Can even think of some more variations to avoid this. Not sure, though, that this could ever occur. So, perhaps it's totally irrelevant.

Resources