If cell contains 'text' within 'string' then output - ??
Posted by Aron Gahagan on December 15, 2001 10:34 PM
Hi,
I can't seem to figure out how to search a cell for text within a string (e.g. for 'Aqua' in string NCHHFIRO - Aquaking Color - Product number is never the same length. I've tried Search(), Find(), Match(), etc. but most require search in arrays or at fixed position in cell (start_num). Any ideas?
Posted by Tom Urtis on December 15, 2001 11:19 PM
Based on the title of your post, it sounds like you want to search a cell for a particular portion of whatever entire text exists in that cell, and then display an output that evaluates to true or to false, depending on whether or not you find that text portion.
One such formula could be:
=IF(SUMPRODUCT(ISNUMBER(SEARCH("Tom",A1:A10))+0),True,False) [many thanks to Aladin for helping me with this], where you'd substitute the True and False statements for whatever else you want to display or otherwise calculate.
Note that this formula would evaluate to true if the text contains "tom", "Tom", "tomorrow", or "stomp".
This formula example has a range argument of A1:A10, so you'd only need to enter it in one cell to evaluate for a range, but if you want to evaluate based on individual cells (say in column B based on text in corresponding column A), then just enter:
=IF(SUMPRODUCT(ISNUMBER(SEARCH("Tom",A1))+0),True,False) in B1 and copy down to B10 or as needed.
There is a way to do this using VBA, but maybe / hopefully this is what you are looking for. If not please repost.
Tom Urtis
Posted by Aron on December 16, 2001 10:36 PM
Perfect!! Thanks!
