Forum Discussion
Solved
Formula required – if it exists
Hi everyone Last year someone on here gave me the most brilliant formula for something, and I am back here again with similar hopes! Is there a way of telling Excel, if Col A contains the wor...
This is my rendition:
=LET(article,LEFT(A1,FIND(" ",A1)-1),const,{"el","m";"la","f";"las","f, pl";"los","m, pl"},VLOOKUP(article,const,2,0))
OliverScheurich Hi. This is great. I modified it a bit and added "los" too, and also change the pl to f,pl and m,pl. Then I tested it on the sample below. The problem is, as you can see from the attached screenshot, if there is an "el" anywhere in the cell, as part of another word, it prioritises this and gives m. So 1 and 7 are incorrect. Is there a way to fix this too? Thank you!
=IF(ISNUMBER(SEARCH("el ",A1)),"m",IF(ISNUMBER(SEARCH("las ",A1)),"f pl",IF(ISNUMBER(SEARCH("la ",A1)),"f",IF(ISNUMBER(SEARCH("los ",A1)),"m pl",""))))An alternative for Patrick2788 solution could be above formula. The formula checks if e.g. "el " (with a space in the end) exists in the cell.
Yes, that one works too. Thank you so much, both of you! What an amazing community there is out there. I really appreciate it!