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.
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!
lucahla The list of 6 snippets can be in any order. If the first one BC and the second one is AB, the formula will return 1 is the text string contains BC and a 2 it it contains AB. If this is not what you need, I have misunderstood your requirement.