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
Pat
  • 5
  • 2

2 Answers2

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

FlexYourData
  • 6,430
  • 2
  • 6
  • 21
  • 1
    Nice one +10 ☺,,,, but VLOOKUP has some issue !! – Rajesh Sinha Jan 02 '23 at 05: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 Jan 02 '23 at 12:37
  • ,,,, yeah no issue,,,, I was mistaken ☺ – Rajesh Sinha Jan 03 '23 at 05: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 Jan 03 '23 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 Jan 03 '23 at 20:23
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","")))

Reddy Lutonadio
  • 17,120
  • 4
  • 14
  • 35
  • 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 Jan 03 '23 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](https://superuser.com/help/someone-answers). Regarding your "proposed" formula, I think it is better to use the [FILTER function](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759). – Reddy Lutonadio Jan 03 '23 at 19:41