Forum Discussion
Categorizing in Excel
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.
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."
yes, I can show a snippet that looks like mine (I can't share any original text or numbers).
So, let's assume C2 contains "A123456-78_9 BC".
Let's also say C3 contains "A987654-32_1 DE".
I then want to have Excel give me
for D2: "1"
for D3: "2"
etc.
also, to clarify: there are roughly 500-1000 rows that look like this, each one with slightly different combinations, but the last 2 signs are alwas one of only 6 different options.
Do you see what I mean? Thank you in advance!
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.
Riny_van_Eekelen Yes, thanks for that. I looked at it and it came kind of close to what I'm looking for, but didn't really work, because the snippets are not alphabetically sorted. It would then give me positions of the snippets, but that wasn't what I was really looking for. But thank you already, I will try and play around with the new tips you mentioned!
lucahlaI get it but I can't guess what those 6 combinations are and what # to assign to each.
I recommend you set up a table somewhere on an existing sheet or add a new sheet and list those 6 items and the # you want to be displayed next to it. Let's pretend you add it to cells A2:B7 of a new sheet called 'LookUpSheet'. Then you can use the following formula starting in D2:
=VLOOKUP(MID(C2,SEARCH(" ",C2),99),'LookUpShet'!$A$2:$B$7,2,1)