EXCEL -Using Wildcards in If Then statements 2

[jen9814]

I am trying to find a certain word in a cell using an if then statement. I would like to find if the cell contains "yes" anywhere in the cell and if it does to name the cell "yes". I would appreciate any help. Thanks.

 

[SkipVought]

Hi,

Do you REALLY mean that you want to NAME the cell "YES"

UNLESS...

there is ONLY ONE CELL containing that string???

Only ONE range on a sheet can have a specific name like "Yes"

???



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[hneal98]

try the SEARCH function. You should be able to look its use up in Excel Help.

 

[DennisFromBoulder]

Try the IF function that follows to determine whether a cell has the consecutive letters "yes" in it, but you can't assign a range name to it without doing so manually or incorporating that action into a macro.

=IF(ISERR(FIND("yes",E2)),"","yes&quot

This function either leaves the current cell with a null string (appearing blank) or with the text string "yes".

 

[jen9814]

Thank you very much for the help. The function =IF(ISERR(FIND("yes",E2)),"","yes&quotworked great!

 

[jen9814]

I spoke too soon that the function worked. Unfortunately it only works when yes is first word in the cell or if it is exactly "yes". Sometimes the word yes is in various locations in the cell and sometimes has a "-" or other characters attached to the word. I am just trying to do this with one column. Any suggestions?

 

[xlbo]

sorry but nope - the find function doesn't work like that - just tested the formula on a bunch of different examples and worked on all of them. That formula will return a yes if any 3 characters in the cell show yes.

Tested and works for
aera c yes sdfasefa
\serfyesasetr
a\dsr\ yes-werte
yes
a\f yes

amongst others

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[jen9814]

Agree that the function does work with the examples listed above but for some reason it doesn't work with these examples.

Yes - Pivot
Yes-Pivot

 

[xlbo]

It's because they have a capital Y
change formula to:
=IF(ISERR(FIND("YES",upper(E2))),"","yes&quot

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[GlennUK]

If you use SEARCH instead of FIND, you don't need to worry about tests being case sensitive.

Glenn.

 

[jen9814]

The formula =IF(ISERR(FIND("YES",upper(E2))),"","yes&quot and using SEARCH worked great. Thank all of you for your help!