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

[irman]

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

 

[Marcelo Branco]

Try

B1
=LEFT(A1)

Hope this helps

M

 

[irman]

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

 

[Aladin Akyurek]

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)

 

[SyedUsman]

=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