Apple Intelligence is now available on iPhone, iPad, and Mac!

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

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
Question marked as Top-ranking reply

Posted on Jun 26, 2020 1:23 AM

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

2 replies
Question marked as Top-ranking reply

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

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.