SOLVED

HOW TO: "If cell contains specific text then return specific text"

Copper Contributor

I'm trying to extract a bunch of specific text combinations from cells and present them in a new cell for a database I'm working on for a dental marketing agency.

This formula comes close to working:

=IF(ISNUMBER(SEARCH("Scheduled",Y2,1)),"Scheduled",

IF(ISNUMBER(SEARCH("Not Scheduled",Y2,1)),"Not Scheduled",

IF(ISNUMBER(SEARCH("Reschedule",Y2,1)),"Reschedule",

IF(ISNUMBER(SEARCH("Cancellation",Y2,1)),"Cancellation",

IF(ISNUMBER(SEARCH("Confirmation,Y2,1)),"Confirmation",

IF(ISNUMBER(SEARCH("Attempt Schedule",Y2,1)),"Attempt Schedule",

IF(ISNUMBER(SEARCH("Attempt Reschedule",Y2,1)),"Attempt Reschedule","No Match")))))))

 

However, for the cells containing "Not Scheduled" - "Scheduled" is returned. For cells containing "Attempt Reschedule" - "Reschedule" is returned. 

 

Any fix to this?

 

@Sergei Baklan 

 

 

2 Replies
best response confirmed by TJ_Excel_Newbie (Copper Contributor)
Solution

@TJ_Excel_Newbie 

=IF(ISNUMBER(SEARCH("Not Scheduled",Y2,1)),"Not Scheduled",IF(ISNUMBER(SEARCH("Scheduled",Y2,1)),"Scheduled",IF(ISNUMBER(SEARCH("Attempt Reschedule",Y2,1)),"Attempt Reschedule",IF(ISNUMBER(SEARCH("Cancellation",Y2,1)),"Cancellation",IF(ISNUMBER(SEARCH("Confirmation",Y2,1)),"Confirmation",IF(ISNUMBER(SEARCH("Attempt Schedule",Y2,1)),"Attempt Schedule",IF(ISNUMBER(SEARCH("Reschedule",Y2,1)),"Reschedule","No Match")))))))

You can try this formula which works in my sheet.

schedule.JPG

 

 

=IF(ISNUMBER(SEARCH("Not Scheduled",Y2,1)),"Not Scheduled",IF(ISNUMBER(SEARCH("Scheduled",Y2,1)),"Scheduled",IF(ISNUMBER(SEARCH("Attempt Reschedule",Y2,1)),"Attempt Reschedule",IF(ISNUMBER(SEARCH("Cancellation",Y2,1)),"Cancellation",IF(ISNUMBER(SEARCH("Confirmation",Y2,1)),"Confirmation",IF(ISNUMBER(SEARCH("Attempt Schedule",Y2,1)),"Attempt Schedule",IF(ISNUMBER(SEARCH("Reschedule",Y2,1)),"Reschedule","No Match")))))))

 


 

1 best response

Accepted Solutions
best response confirmed by TJ_Excel_Newbie (Copper Contributor)
Solution

@TJ_Excel_Newbie 

=IF(ISNUMBER(SEARCH("Not Scheduled",Y2,1)),"Not Scheduled",IF(ISNUMBER(SEARCH("Scheduled",Y2,1)),"Scheduled",IF(ISNUMBER(SEARCH("Attempt Reschedule",Y2,1)),"Attempt Reschedule",IF(ISNUMBER(SEARCH("Cancellation",Y2,1)),"Cancellation",IF(ISNUMBER(SEARCH("Confirmation",Y2,1)),"Confirmation",IF(ISNUMBER(SEARCH("Attempt Schedule",Y2,1)),"Attempt Schedule",IF(ISNUMBER(SEARCH("Reschedule",Y2,1)),"Reschedule","No Match")))))))

You can try this formula which works in my sheet.

schedule.JPG

 

 

=IF(ISNUMBER(SEARCH("Not Scheduled",Y2,1)),"Not Scheduled",IF(ISNUMBER(SEARCH("Scheduled",Y2,1)),"Scheduled",IF(ISNUMBER(SEARCH("Attempt Reschedule",Y2,1)),"Attempt Reschedule",IF(ISNUMBER(SEARCH("Cancellation",Y2,1)),"Cancellation",IF(ISNUMBER(SEARCH("Confirmation",Y2,1)),"Confirmation",IF(ISNUMBER(SEARCH("Attempt Schedule",Y2,1)),"Attempt Schedule",IF(ISNUMBER(SEARCH("Reschedule",Y2,1)),"Reschedule","No Match")))))))

 


 

View solution in original post