I have a single cell that contains several sets of text separated by a semi colon (extracted from another application) for example

Cell A1: apple;banana;orange Cell A2: banana;orange;melon

I am trying to introduce a formula in an adjacent cell which will return a value for apple if it contains apple, or banana if it contains banana.

I am happy to have it only return a single value (the first one perhaps)

I tried the IF function using <> as suggested on the internet. I thought this had worked to start with but then it didnt return the value when I moved it! - so it was clearly wrong.

=IF(A1<>"apple","apple","") - returned apple - but then always returned apple even if there was no apple in the string.

I did then try an IFS nested statement - but that didnt work - highly likely because I then realised that even my IF statement didnt work properly. I tried to research ISNUMBER and a couple of other suggestions but cant seem to get it anywhere near working!

It seems like it is something relatively straightforward and I am just missing the obvious - would welcome any assistance.

CodePudding user response：

=IF(ISNUMBER(SEARCH("apple",D365)),"apple",IF(ISNUMBER(SEARCH("melon",D365)),"melon","no")) works :)

CodePudding user response：

Create a list of items to search (in this example, I put that list in cells E2:E4). Then in cell B1 and copied down is this formula:

=TEXTJOIN(";",TRUE,REPT($E$2:$E$4,COUNTIF(A1,"*"&$E$2:$E$4&"*")))