Search and code

[Closed]
Report
Posts
2
Registration date
Friday July 28, 2017
Status
Member
Last seen
July 28, 2017
-
Posts
6
Registration date
Friday July 28, 2017
Status
Member
Last seen
July 31, 2017
-
Hello, so I have this problem in excel where I need to compare the words that are in a certain cell and compare them to the separated word if it finds the word in the combined field it will put 1 in the specific field. so basically, the formula would run through B:G and H is the combined words that are needed to be looked upon B:G


http://static.ccm2.net/ccm.net/pictures/AYAHxuhUpYqBMk0TjuguVuE3Kkl7HOFov78EOZABPYmjYqAYwO0ihppAKu6jpLcI-capture21.png

2 replies

Posts
6
Registration date
Friday July 28, 2017
Status
Member
Last seen
July 31, 2017

Use the following formula:
=IF(ISNUMBER(SEARCH(B10,$H$12)),1,"")
Posts
2
Registration date
Friday July 28, 2017
Status
Member
Last seen
July 28, 2017

Hello stan, this looks awesome but it would be more awesome if it's case sensitive. if it isnt too much to ask :D
Posts
6
Registration date
Friday July 28, 2017
Status
Member
Last seen
July 31, 2017
>
Posts
2
Registration date
Friday July 28, 2017
Status
Member
Last seen
July 28, 2017

If you want this to be case sensitive, use FIND instead of SEARCH.

So the formula becomes: =IF(ISNUMBER(FIND(B10,$H$12)),1,"")

If you wish to have case sensitive, it is already there. You do understand that case sensitive means that "that" and "thaT" are two different things? Why limit your string matches to HOW the user types. You will end up with the following result:

"My user typed in 'aCtive', but I was looking for 'Active'."