How to search multiple text from a cell and return back specific value in the following cell

irman

New Member
Joined
Aug 25, 2017
Messages
3
Hi guys

I need a formula



if Cell A contain TOUCHSCREEN return value "T" if its contain NO DISPLAY Return value "N" if its contain AUTO RESTART return value "A" if its contain LINES return value "L"



Call A1 Cell B1

TOUCHSCREEN T
NO DISPLAY N
AUTO RESTART A
LINES L
CRACK C
BSOD B
ODD O
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Try

B1
=LEFT(A1)

Hope this helps

M


Hi Marc

Thanks for the reply but I mean if the Cell A1 contains a specific text I can get return back my defined text in Cell B2

this is my formula

=IF(OR(ISNUMBER(SEARCH({"RESTART","POWER ON"," SHUTDOWN","TOUCH"},AF2))), I need to get here is Cell A1 contain RESTART return back RESTART in Cell B2 if contain POWER ON return back POWER ON and so on and the number of keyword I want to use is more that 60
 
Upvote 0
Hi Marc

Thanks for the reply but I mean if the Cell A1 contains a specific text I can get return back my defined text in Cell B2

this is my formula

=IF(OR(ISNUMBER(SEARCH({"RESTART","POWER ON"," SHUTDOWN","TOUCH"},AF2))), I need to get here is Cell A1 contain RESTART return back RESTART in Cell B2 if contain POWER ON return back POWER ON and so on and the number of keyword I want to use is more that 60

Create a range with the keywords and name that list keywords via Name Manager. Also, sort the range in ascending order.

Now invoke:

Either

=LOOKUP(9.99999999999999E+307,SEARCH(keywords,AF2),keywords)

Or... if needed

=LOOKUP(9.99999999999999E+307,SEARCH(" "&keywords&" "," "&AF2&" "),keywords)
 
Upvote 0
Code:
=IF(MAX(--(ISNUMBER(SEARCH([B]$H$1:$H$4[/B],A1))))=1,A1,"")

Where H1:H4 is the list of keywords , change it to accommodate the 60 keywords
Use with CTRL SHIFT ENTER
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

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