If then using wildcard

[ec1957]

Have a cell that says something like "123 main street".
I want the cell next to it to = Main if it detects main in the other cell and if not then it should say no. I tried this.

=if(B2="*main","Main","No"

However, it is not working. Any thoughts.

 

[Peter_SSs]

Like this?

Excel Workbook
	A	B
1
2	123 main st	Main
3	123 back st	No
4	123 Quartermaine st	No
Check Text

 

[ec1957]

Wow, that was quick. Thank you very much. I am going to see if I can get it to work with multiple requests so if it has main then X and if it has road then x. Thanks again.

 

[Peter_SSs]

Wow, that was quick. Thank you very much. I am going to see if I can get it to work with multiple requests so if it has main then X and if it has road then x. Thanks again.

OK, but if you need further help with this, you would need to spell that out in more exact detail. Also a little more sample data and expected results would help clarify.

 

[xenou]

Hi,
Using Search() with IsNumber() will work I think:
=IF(ISNUMBER(Search("main",B2)),"main","no")

Search returns the number of the starting position of the searched text - so if a number is returned, we know it was found. Funny, I thought the wildcard would work too - scratched my head for a while on that.

Alex

Edit: Lol - while scratching my head Peter had posted the answer almost immediately!

 

[Peter_SSs]

Funny, I thought the wildcard would work too - scratched my head for a while on that.

You could use wildcards ...

=IF(COUNTIF(A2,"* main *"),"main","no")

...but my (limited) understanding is that COUNTIF uses quite a lot of resources, so I would generally go with the ISNUMBER(SEARCH(... method.

 

[ec1957]

Hmmmm. What about if your are trying to do this.

Column A has cells that have addresses.
123 Main Street
234 Presidente Road
987 Forest Drive

Now, in Column B you want it to equal Main if it appears in Column A, Road if Road appears in Column A, Drive if Drive appears in column A.

Basicly, looking for multiple instances of a value instead of just how you helped me look at one?

 

[Peter_SSs]

Hmmmm. What about if your are trying to do this.

Column A has cells that have addresses.
123 Main Street
234 Presidente Road
987 Forest Drive

Now, in Column B you want it to equal Main if it appears in Column A, Road if Road appears in Column A, Drive if Drive appears in column A.

Basicly, looking for multiple instances of a value instead of just how you helped me look at one?

My understanding then is that for those three example, in column B you would expect
Main
Road
Drive

1. Can you confirm the above?

2. What results would you expect in column B for these entries in column A?
234 Smith Street
234 Smith Rd
123 Main Road

 

[GMorris]

Maybe I'm missing something here, but there IS a function named Proper() which will convert the case to a capital first, then lower letters, like main=Main, street=Street, etc. It's worked for me many times!

 

[ec1957]

Sorry, I think I'm making it more confusing.

I want to be able to have a cell in column B look into a cell in column A. I want it to attempt to find any one of three different variables. Depending on the variable I have it looking for, the cell in column B would then = X.