# Check if a cell has these string of words...



## thp510 (Jan 4, 2023)

Hi. I'm having trouble trying to determine what function I can use to see if a cell has either the words "Modernization" (sometimes "modernize" for short) or "Migration" (sometimes using "migrate" for short). If it has any of those words in column A, I would like the cells in column B to say "Yes", otherwise it should say no. Here's an example of what I would like to see in column B below. Any suggestions on how I can do this? 


AB1This modernization is currentYes2We will modernize at a later timeYes3Uplift projectNo4Migrate end of yearYes5Migration is already happeningYes6Standstill for nowNo


----------



## etaf (Jan 4, 2023)

how about

=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH({"modernization","Migration","Modernize","migrate"},A2)))))>0,"Yes","No")
OR
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH({"modernization","Migration","Modernize","migrate"},A2))))>0,"Yes","No")


Book8ABCD12This modernization is currentYesYesYes3We will modernize at a later timeYesYesYes4Uplift projectNoNoNo5Migrate end of yearYesYesYes6Migration is already happeningYesYesYes7Standstill for nowNoNoNoSheet1Cell FormulasRangeFormulaC2:C7C2=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH({"modernization","Migration","Modernize","migrate"},A2)))))>0,"Yes","No")D2:D7D2=IF(SUMPRODUCT(--(ISNUMBER(SEARCH({"modernization","Migration","Modernize","migrate"},A2))))>0,"Yes","No")


----------



## kweaver (Jan 4, 2023)

How about:

Book1ABCD1This modernization is currentYesYesmodernization2We will modernize at a later timeYesYesmodernize3Uplift projectNoNomigration4Migrate end of yearYesYesmigrate5Migration is already happeningYesYes6Standstill for nowNoNoSheet1Cell FormulasRangeFormulaC1:C6C1=IF(OR(TRUE=ISNUMBER(SEARCH($D$1:$D$4,A1))),"Yes","No")


----------



## thp510 (Jan 4, 2023)

etaf said:


> how about
> 
> =IF(SUMPRODUCT(--(NOT(ISERR(SEARCH({"modernization","Migration","Modernize","migrate"},A2)))))>0,"Yes","No")
> OR
> ...


Thank you. However, do I need to hit ctrl + shift + enter when doing this forumla or add the { } brackets manually?


----------



## etaf (Jan 4, 2023)

Depends on the version of excel you have 
later versions automatically work with arrays 

is it not working


----------



## thp510 (Jan 4, 2023)

etaf said:


> Depends on the version of excel you have
> later versions automatically work with arrays
> 
> is it not working


Thanks! It works and I didn't have to hit ctrl + shift + enter (it didn't work when I did use the combo). Just a bit perplexed trying to figure out how those array functions work when usually I have to hit that ctrl + shift + enter combo. Even more confusing when those squiggly brackets are within a formula versus usually being outside of all the formulas.


----------



## thp510 (Jan 4, 2023)

kweaver said:


> How about:
> 
> Book1ABCD1This modernization is currentYesYesmodernization2We will modernize at a later timeYesYesmodernize3Uplift projectNoNomigration4Migrate end of yearYesYesmigrate5Migration is already happeningYesYes6Standstill for nowNoNoSheet1Cell FormulasRangeFormulaC1:C6C1=IF(OR(TRUE=ISNUMBER(SEARCH($D$1:$D$4,A1))),"Yes","No")


Thank you. This also worked!


----------



## kweaver (Jan 4, 2023)

thp510 said:


> Thank you. This also worked!


You could also put the array in place of the reference to D1:D4

Book1ABCDE1This modernization is currentYesYesmodernizationYes2We will modernize at a later timeYesYesmodernizeYes3Uplift projectNoNomigrationNo4Migrate end of yearYesYesmigrateYes5Migration is already happeningYesYesYes6Standstill for nowNoNoNoSheet1Cell FormulasRangeFormulaC1:C6C1=IF(OR(TRUE=ISNUMBER(SEARCH($D$1:$D$4,A1))),"Yes","No")E1:E6E1=IF(OR(TRUE=ISNUMBER(SEARCH({"modernization","modernize","migration","migrate"},A1))),"Yes","No")


----------



## etaf (Jan 4, 2023)

you dont say what version of excel you have - worth adding to your profile , so many new functions now have been added - solutions will differ a lot 

its not an array formula - because the array is specified in the formula 
{} 
so that is testing as an OR 
"modernization","modernize","migration","migrate"},
searching for the array 
returns a TRUE,FALSE array 

you can see in an example if you just put the search in
Book1ABCDEFGHIJK12This modernization is current, modernize Migrate YesYesYes6#VALUE!32423We will modernize at a later timeYesYesYes#VALUE!#VALUE!9#VALUE!4Uplift projectNoNoNo#VALUE!#VALUE!#VALUE!#VALUE!5Migrate end of yearYesYesYes#VALUE!#VALUE!#VALUE!16Migration is already happeningYesYesYes#VALUE!1#VALUE!#VALUE!7Standstill for nowNoNoNo#VALUE!#VALUE!#VALUE!#VALUE!Sheet2Cell FormulasRangeFormulaC2:C7C2=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH({"modernization","Migration","Modernize","migrate"},A2)))))>0,"Yes","No")D2:D7D2=IF(SUMPRODUCT(--(ISNUMBER(SEARCH({"modernization","Migration","Modernize","migrate"},A2))))>0,"Yes","No")H2:K7H2=SEARCH({"modernization","Migration","Modernize","migrate"},A2)Dynamic array formulas.

Search returns a number 
ISNUMBER - true or false 

and then OR - will test the array and return  TRUE or FALSE 


But something like 
=SUM(LEN(A13:A19))
is an ARRAY formula as its referencing a range in a function that normally only has a cell 
so needs the {} 
BUT as i say later versions of excel - automatically do that now -


----------



## Fluff (Jan 5, 2023)

@thp510 What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


----------

