Forum Discussion
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
"12345678-snippet".
The snippet can be one of 6 options. I now want to have excel categorize this into a column "D".
The desired result would be having each cell in column "D" to
- look at it's neighbour cell in "C"
- look for the text snippet while ignoring the numbers and then
- displaying a result like "1" if there is snippet 1 in "C", "2" for snippet option 2 etc.
Is this possible? I was looking at functions like ISTEXT, IF and IFS but couldn't wrap my head around it.
Your help would be much appreciated!
Luca
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.
9 Replies
- you say there are 6 options. can you list or give details on what they are. is that whole '12345678' what you are referring to? so there are basically 6 possible 8 digit id #s that will be followed by some text or comment? for each of those 6 options what do you want in col D? and what if you don't find a 'valid' item? ignore or show error?
they are 6 to 7 digit long combinations of numbers and text, then there is a space, and then there is the snippet that has to be indexed, so, with an example snippet "AB":
"12k45f7 AB". The letter/digit combinations can be ignored, I just want to index/categorise the snippets. So, said in a text form, something like:
"if cell C4 contains the the letter combination that reads "AB" (just as an example), then you should output number "3" (example) to cell D4."
lucahla Perhaps the attached filed contains the answer to your question.
