Formula required – if it exists

[Christine_S_]

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.

[#2]

@Christine_S_ 

=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.

 

[#3]

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.

[#4]

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!

[#5]

@Patrick2788 

Hi Patrick! Many thanks.
I am in 365, version 16.54, on a Mac.

Sample attached....

 

[#6]

@Christine_S_ 

=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.

 

[#7]

@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!

 

[#8 · Accepted solution]

@Christine_S_ 

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))

[#9]

@Christine_S_ 

=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.

[#10]

@Patrick2788 Yes, that one worked! Thanks a million!

[#11]

@OliverScheurich 

Yes, that one works too. Thank you so much, both of you! What an amazing community there is out there. I really appreciate it!

[#12]

You're welcome. Glad it worked!