Question description:
This user has given permission to use the problem statement for this
blog.
I want to check if cell contain any of the words I define as variable for example then return this word
Solved by G. A. in 30 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
10/06/2018 - 08:04
Hello
User
10/06/2018 - 08:04
Hello
Excelchat Expert
10/06/2018 - 08:05
Welcome to excelchat, we are happy to have you today with us.
User
10/06/2018 - 08:05
Have you seen my question?
Excelchat Expert
10/06/2018 - 08:05
Yeah, I did. I saw your question.
Excelchat Expert
10/06/2018 - 08:06
I believe you want to search a word in a string and if the word is there, return another word, right?
Excelchat Expert
10/06/2018 - 08:06
Please correct me if I'm wrong.
User
10/06/2018 - 08:06
Return the same word
Excelchat Expert
10/06/2018 - 08:07
Okay, can you please show me an example? In that way I can be sure of the exact problem.
User
10/06/2018 - 08:08
so I want B11 to check if G11 have got balloon or baseball or basketball or tennisball to return the word that it found
Excelchat Expert
10/06/2018 - 08:10
The word's you are gonna search are limited to only those words or it can be anything? Also, would you like to search once or you would test single word once at a time?
User
10/06/2018 - 08:11
For example I have 35 other words to search for
User
10/06/2018 - 08:11
And I would like to search once
Excelchat Expert
10/06/2018 - 08:13
Okay, I believe I know what you are looking for. Please note that the solution I'm talking about it formula only solution and for the solution to work, we need to list all those different words that you would be searching for.
User
10/06/2018 - 08:13
Should I list them in same sheet?
User
10/06/2018 - 08:14
Can't I import them from another excel file?
Excelchat Expert
10/06/2018 - 08:14
You can list them in another sheet.
Excelchat Expert
10/06/2018 - 08:15
Like create a new sheet and put them on that sheet.
Excelchat Expert
10/06/2018 - 08:15
For example, I have just created one "Sheet2" in the preview window.
User
10/06/2018 - 08:16
Ok import them from sheet 2 please
User
10/06/2018 - 08:16
Yea exactly
Excelchat Expert
10/06/2018 - 08:16
Please give me a few minutes.
Excelchat Expert
10/06/2018 - 08:22
I need some more time, please stay with me.
User
10/06/2018 - 08:22
The timer is about to finish
Excelchat Expert
10/06/2018 - 08:23
You will be asked to extend time, please select YES>
User
10/06/2018 - 08:23
I closed it
Excelchat Expert
10/06/2018 - 08:23
Ah, I see. Well we can continue.
Excelchat Expert
10/06/2018 - 08:23
Although you won't be able to reply back.
User
10/06/2018 - 08:24
I can reply on sheet
Excelchat Expert
10/06/2018 - 08:24
Alternatively you can write the preview window.
Excelchat Expert
10/06/2018 - 08:24
Yeah, you can. Please give me some more time.
Excelchat Expert
10/06/2018 - 08:26
Thanks for waiting. I have wrote a formula.
Excelchat Expert
10/06/2018 - 08:26
Here it is.
Excelchat Expert
10/06/2018 - 08:26
=IF(ISNUMBER(SEARCH("*"&Sheet2!A1&"*",G11)),Sheet2!A1,IF(ISNUMBER(SEARCH("*"&Sheet2!A2&"*",G11)),Sheet2!A2,IF(ISNUMBER(SEARCH("*"&Sheet2!A3&"*",G11)),Sheet2!A3,IF(ISNUMBER(SEARCH("*"&Sheet2!A4&"*",G11)),Sheet2!A4,""))))
Excelchat Expert
10/06/2018 - 08:26
Now, this formula is only for the 4 words listed in sheet2.
Excelchat Expert
10/06/2018 - 08:26
Please see B11 now.
Excelchat Expert
10/06/2018 - 08:27
Yeah, we have to add IF(ISNUMBER(SEARCH("*"&Sheet2!A4&"*",G11)),Sheet2!A4, for all new words.
Excelchat Expert
10/06/2018 - 08:28
Then have to add 35 nested IF.
Excelchat Expert
10/06/2018 - 08:29
It might take some time to write the formula for the first time, but once you have it written, you won't need to change it.
Excelchat Expert
10/06/2018 - 08:32
There is a very short formula which would be able to detect whether the word's are present in G11 or not, but that formula won't be able to return which word is actually there.
Excelchat Expert
10/06/2018 - 08:32
=SUMPRODUCT(--ISNUMBER(SEARCH(Sheet2!A1:A4,G11))) is the formula
Excelchat Expert
10/06/2018 - 08:32
I have wrote it in D9
Excelchat Expert
10/06/2018 - 08:33
1 means at least one word from sheet2 is present in G11
Excelchat Expert
10/06/2018 - 08:34
Thanks for visiting us today, I will close this chat now.
Excelchat Expert
10/06/2018 - 08:35
Have a great day.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
