Forumla Combination

[noochi]

Hi,

I have an xlsx that is an export of login data. I'm trying to search field A2 for specific text, and if it true, write something in the cell where I'm running the formula from, D2. The first example below works great, but if the cell says contains multiple words such as "1 brown apple", 'a very ripe avocado" (you get the point), the formula returns nothing. If the cell contains only the word apple, then it works great. I was trying to combine the first formula w the second formula below, but no luck.


=IF(A2="apple", "Ap", IF(A2="avocado", "Av", IF(A2="banana", "B", IF(A2="Lemon", "L", ""))))

=IF(AND(ISNUMBER(SEARCH("apple",A2)),ISNUMBER(SEARCH("log",A2))),"Valid ","")


Can anyone assist in searching a cell that has multiple pieces of text (imagine a whole sentence) and write the results to a cell?


Thank you so much! My xlsx skills are ok, but no match for the internet!

[Pete_UK]

It would help if you attached a sample Excel workbook, then we can see how your data is laid out. To do this, just follow the guidelines given in the yellow banner near the top of the screen, which I have reproduced below:

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Hope this helps.

Pete

[AliGW]

Administrative Note:

Is your forum profile showing the Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

[noochi]

Sample Apple Worksheet.xlsx

File Attached!

[Pete_UK]

You can use this formula in D2:

=IF(ISNUMBER(SEARCH("apple",A2)),"Ap ","")& IF(ISNUMBER(SEARCH("avocado",A2)),"Av ","")& IF(ISNUMBER(SEARCH("banana",A2)),"B ","")& IF(ISNUMBER(SEARCH("lemon",A2)),"L ","")

then copy down. If you had multiple words from your list in one cell, like "apple and avocado", then you will get Ap Av as the result.

Hope this helps.

Pete

[noochi]

Pete, you're rockstar, thank you! I'm going to give this a go right now!

Thanks!

[noochi]

A quick update, the formula works great!! One follow up question that I didn't think of... What if I wanted to add to the formula to also search for the phrase "green apple" in addition to "apple"? How would I account for the both words with a space between them?

Thank you!!

[Pete_UK]

If that is a separate entry, for which you want to return some code like Ga, then you could amend the formula like this:

=IF(ISNUMBER(SEARCH("apple",A2)),"Ap ","")& IF(ISNUMBER(SEARCH("avocado",A2)),"Av ","")& IF(ISNUMBER(SEARCH("banana",A2)),"B ","")& IF(ISNUMBER(SEARCH("lemon",A2)),"L ","")& IF(ISNUMBER(SEARCH("green apple",A2)),"Ga ","")

However, if you had green apple together in a cell the formula would then produce Ap Ga, as "apple" is a subset of the entry, which is probably not what you want.

It's getting a bit late here, and tomorrow I'm off for a long weekend, so I won't be able to contribute anything else until Tuesday. But, if that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[AliGW]

Are you still using Excel 2003? Please don't ignore my question this time.

[wk9128]

Cell D2 formula , Drag down

Please Login or Register  to view this content.