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

T

thp510

Board Regular
Joined
Oct 19, 2015
Messages
110
Office Version
  1. 365
Platform
  1. Windows
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?

AB
1This modernization is currentYes
2We will modernize at a later timeYes
3Uplift projectNo
4Migrate end of yearYes
5Migration is already happeningYes
6Standstill for nowNo
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
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")


Book8
ABCD
1
2This modernization is currentYesYesYes
3We will modernize at a later timeYesYesYes
4Uplift projectNoNoNo
5Migrate end of yearYesYesYes
6Migration is already happeningYesYesYes
7Standstill for nowNoNoNo
Sheet1
Cell Formulas
RangeFormula
C2: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")
 
Upvote 0
Solution
How about:

Book1
ABCD
1This modernization is currentYesYesmodernization
2We will modernize at a later timeYesYesmodernize
3Uplift projectNoNomigration
4Migrate end of yearYesYesmigrate
5Migration is already happeningYesYes
6Standstill for nowNoNo
Sheet1
Cell Formulas
RangeFormula
C1:C6C1=IF(OR(TRUE=ISNUMBER(SEARCH($D$1:$D$4,A1))),"Yes","No")
 
Upvote 0
etaf said:
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")


Book8
ABCD
1
2This modernization is currentYesYesYes
3We will modernize at a later timeYesYesYes
4Uplift projectNoNoNo
5Migrate end of yearYesYesYes
6Migration is already happeningYesYesYes
7Standstill for nowNoNoNo
Sheet1
Cell Formulas
RangeFormula
C2: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")
Click to expand...
Thank you. However, do I need to hit ctrl + shift + enter when doing this forumla or add the { } brackets manually?
 
Upvote 0
Depends on the version of excel you have
later versions automatically work with arrays

is it not working
 
Upvote 0
etaf said:
Depends on the version of excel you have
later versions automatically work with arrays

is it not working
Click to expand...
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.
 
Upvote 0
kweaver said:
How about:

Book1
ABCD
1This modernization is currentYesYesmodernization
2We will modernize at a later timeYesYesmodernize
3Uplift projectNoNomigration
4Migrate end of yearYesYesmigrate
5Migration is already happeningYesYes
6Standstill for nowNoNo
Sheet1
Cell Formulas
RangeFormula
C1:C6C1=IF(OR(TRUE=ISNUMBER(SEARCH($D$1:$D$4,A1))),"Yes","No")
Click to expand...
Thank you. This also worked!
 
Upvote 0
thp510 said:
Thank you. This also worked!
Click to expand...
You could also put the array in place of the reference to D1:D4

Book1
ABCDE
1This modernization is currentYesYesmodernizationYes
2We will modernize at a later timeYesYesmodernizeYes
3Uplift projectNoNomigrationNo
4Migrate end of yearYesYesmigrateYes
5Migration is already happeningYesYesYes
6Standstill for nowNoNoNo
Sheet1
Cell Formulas
RangeFormula
C1: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")
 
Upvote 0
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
Book1
ABCDEFGHIJK
1
2This modernization is current, modernize Migrate YesYesYes6#VALUE!3242
3We will modernize at a later timeYesYesYes#VALUE!#VALUE!9#VALUE!
4Uplift projectNoNoNo#VALUE!#VALUE!#VALUE!#VALUE!
5Migrate end of yearYesYesYes#VALUE!#VALUE!#VALUE!1
6Migration is already happeningYesYesYes#VALUE!1#VALUE!#VALUE!
7Standstill for nowNoNoNo#VALUE!#VALUE!#VALUE!#VALUE!
Sheet2
Cell Formulas
RangeFormula
C2: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 -
 
Upvote 0
@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’)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA check whether a row for the current date has been filled in and return yes/no value in another cell.
Replies
5
Views
209
mumps
mumps
N
  • Question
2-step Dynamic Ranges and Charts
Replies
0
Views
206
Nick Loughton
N
D
  • Question
Excel Word Link - part table Excel to Word, part Word to Excel
Replies
2
Views
93
NdNoviceHlp
NdNoviceHlp
S
  • Solved
Find Text in a cell
Replies
5
Views
138
Fluff
Fluff
S
  • Solved
For each row, create new folder based on single cell value, then save exported XML doc to matching folder.
Replies
3
Views
173
shand
S

Forum statistics

Threads
1,215,042
Messages
6,122,810
Members
449,095
Latest member
m_smith_solihull

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top