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 word "x" then insert the word "y" in Col B?
Does that make sense? I have a spreadsheet of 10,000 Spanish words, all with their article (el, la, los etc). I want to indicate m, f, m pl etc in Col B, then delete all the el, la etc from Col A.
If anyone knows how, or has a better idea than mine, I'd be very glad to hear from you!
Many thanks.
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))
- Everything you've listed sounds do-able.
There are two things needed to give you the best solution possible:
1. Which version of Excel are you using? If you're on 365, the version/build will help determine the functions to which you have access.
2. A small sample of about 10 words. If you could provide a sample of what Column A might look like and then show desired results in Column B, this would be great.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.
