Hi,
I'm trying to tag a column based on what is written in that cell. For example:
Screenshot 2023-01-01 174208.png
As you can see, I have column A with description and column B as my desired result.
I want to go down column A and identify any words/strings that matches my key list. Once it matches it, it return the key as a tag.
I'm currently using nested IFS but its starting to get out of hand.
Thank you for your help
Please Login or Register to view this content.
1. Ensure all terms are consistently separated (, ).
2. Delete all expected results.
3. Use a dynamic array to spill the results down:
Formula:
Please Login or Register to view this content.
Last edited by Glenn Kennedy; 01-01-2023 at 05:33 AM.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi Glenn,
Thank you for your response.
I can't seem to download the workbook.
Edit: all fixed!
Fixed = downloaded, or = downloaded and it works??
Another option:
Please tryFormula:Please Login or Register to view this content.
Here is an improved version of the formula:
Please tryFormula:Please Login or Register to view this content.
Sitting on a damp Sunday afternoon - with bags under my eyes after a late, late night... with the gentlest of headaches - is perhaps not the best time to try to understand recursive lambda functions!!
This is non-recursive.. and I understand it... and it's far better than my first effort. But not as nice as yours, Hans, at Post 6.
=BYROW(A2:A9,LAMBDA(x,INDEX(E2:E4,MAX(IF(ISNUMBER(SEARCH(TEXTSPLIT(TEXTJOIN("|",,F2:F4),", ","|"),x)),SEQUENCE(ROWS(E2:E4)),"")))))
at first i couldn't download it, but it worked.
im noticing, when i use multiple words to search eg animal = dog,cat,rat,bat etc it fails to pick some and depending on the order.
I've attached a sample data i was experimenting with...
I basically changed the order of my key and it failed to pick it up. Depending on the order I put it, it picks it up. ??
Screenshot 2023-01-02 100056.png
Edit: Please ignore, I noticed the problem. I need, its a spacing issue. need dog, cat, snake etc
Last edited by bob112233; 01-01-2023 at 07:17 PM.
Indeed, Glenn also explicitly stated:its a spacing issue. need dog, cat, snakeAnd that applies to all formulas.1. Ensure all terms are consistently separated (, )
You are welcome, thanks for the feedback and rep
Re: Find a particular text within a cell
just for fun
with Power Query
Description Tag ksdjfhsk cat idshofsih Animal dog - @hgios Animal hsodfhs -snake 1231 Animal oihdsd Burj Khalifa 556 Building Opera House 889 Building George 33Human Human 9302 @- ADAM 0jdj Human Parrot 9393 Animal
Recursive lambdas in Excel functions. Who would have guessed that 15 years ago?..
Even now... hangover gone... I don't really understand them!!
yeah, one main problem with them is debugging... you cant easily see how many times the recursion happened.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks