0

I am trying to create a formula that will enter specific texts, when the cell had certain keywords. I've seen a formula here, but I can't seem to add more texts to it.

What should be my formula below? My formula doesn't seem to work:

=IF(ISNUMBER(SEARCH("Mammals",A1)),"Mammals",""),(SEARCH("Reptiles",A1)),"Reptiles",""),(SEARCH("Birds",A1)),"Birds",""))

Keywords = Text

  • Mammals Horse = Mammals
  • Cat Mammals = Mammals
  • Reptiles Frog = Reptiles
  • Dove Birds = Birds
Improve this question

2 Answers 2

Reset to default
0

You are trying to separate the IF, the second and third SEARCH functions with the commas thinking that they will somewhat interact is wrong. You must include them in your IF statement. IF functions can be nested inside an IF function. I have slightly modified your formula to include nested IF and ISNUMBER functions

=IF(ISNUMBER(SEARCH("Mammals",A1)),"Mammals",
IF(ISNUMBER(SEARCH("Reptiles",A1)),"Reptiles",
IF(ISNUMBER(SEARCH("Birds",A1)),"Birds","")))

Improve this answer
2
  • Thanks so much! This actually worked! I was just wondering, since I will have a lot of keywords, would it be possible to do a search for an entire column and match it from to another column as well? Something like this? But this formula didn't work --> =IF(ISNUMBER(SEARCH(Keywords!A:A, A2)), Keywords!B:B, "")
    – Pat
    Commented Jan 3, 2023 at 18:43
  • @Pat If the question has been successfully answered, mark it as solved. See What should I do when someone answers my question. Regarding your "proposed" formula, I think it is better to use the FILTER function.
    – Reddy Lutonadio
    Commented Jan 3, 2023 at 19:41
1

If you are on Excel for Microsoft 365, here are a few options for you:

If the category is always the first word in the text:

This formula in the first row (adjust range as necessary):

=TEXTBEFORE(A1:A4," ")

enter image description here

If the word is somewhere in the text (not necessarily first word) but you potentially have a much longer list of categories:

This formula in the first row (adjust range as necessary):

=XLOOKUP(A1:A4,E1:E3,E1:E3,"",-1)

enter image description here

If you have an older version of Excel

Put your list of categories in a separate range and sort them alphabetically. Then put this formula in the first row and copy it down to the remaining rows:

=VLOOKUP(A1,$E$1:$E$3,1,TRUE)

enter image description here

Improve this answer
5
  • 1
    Nice one +10 ☺,,,, but VLOOKUP has some issue !!
    – Rajesh Sinha
    Commented Jan 2, 2023 at 5:08
  • Thanks, @RajeshSinha. Curious what you mean. I'm sure there will be problems with some data with approximate VLOOKUP, but for the data in the qn, I think it's ok.
    – FlexYourData
    Commented Jan 2, 2023 at 12:37
  • ,,,, yeah no issue,,,, I was mistaken ☺
    – Rajesh Sinha
    Commented Jan 3, 2023 at 5:13
  • Thank you, but this is not really what I'm looking for. I edited the question so it will be clearer. I need a formula to automatically enter a text to certain keywords found in the cell.
    – Pat
    Commented Jan 3, 2023 at 18:47
  • That's what my answer does. The difference is that my answer allows you to manage the list of keywords more easily and not have to embed them in the formula, which will quickly get difficult to manage if the list of keywords grows.
    – FlexYourData
    Commented Jan 3, 2023 at 20:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .