Find a range of text strings to return a text string.

Hi all, first post here so hope this makes sense...


...I need to return a text string in one cell, if one of a range of text strings is found in another cell.


So for example, if the word ‘reception’ or ‘office’ is found in one cell, I would like another cell to return ‘clean windows’. However, it should display nothing if ‘reception’ or ‘office’ are not found. In reality it will be a longer list of possible correct text strings.


I've managed this for one correct lookup but I can’t make it work for a range of possible correct lookups.


Thanks in advance


Pete

MacBook Air 13″, macOS 10.15

Posted on Jun 25, 2020 11:42 PM

Reply
2 replies

Jun 26, 2020 1:23 AM in response to Peterjohnhill

A combination of LEN, nested SUBSTITUTE and IF could do this, but will get unwieldy pretty quickly.


Formula shown below the table is entered in the selected cell ( C2 ), then filled down to the last cell in column C.


Any string in a cell in column D containing one or more of the words in B2, B3 or B3 will return "clean windows". cells in column D containing none of the three key words return a null string ( "" ) as seen in C5.


Regards,

Barry

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Find a range of text strings to return a text string.

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.