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))
=IF(ISNUMBER(SEARCH("el",A2)),SUBSTITUTE(A2,"el","m"),IF(ISNUMBER(SEARCH("las",A2)),SUBSTITUTE(A2,"las","pl"),IF(ISNUMBER(SEARCH("la",A2)),SUBSTITUTE(A2,"la","f"),"")))You can try a nested IF formula.
- Hi Quadruple_Pawn. Many thanks for the quick response! We are definitely on the right track, however, I don't want the word repeated, just the m, pl etc. Is there a way of getting it do do this? Thanks very much!
=IF(ISNUMBER(SEARCH("el",A2)),"m",IF(ISNUMBER(SEARCH("las",A2)),"pl",IF(ISNUMBER(SEARCH("la",A2)),"f","")))You are welcome. This can be done with a shorter nested IF formula.
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!
