finding words in the cell

I

Igneshwara reddy

Hi,

I have to find in one cell, whether any of three words matches. If it
matches, I need to get results as

If it finds word "Spot" in the cell, the answer should reflect as "4", if it
find word "Renewal API" the it should reflect answer as "Renewals" and if
none of the above both words are found it should reflect answers as "others".

Let me know how can I get the desired results.

Regards,
Igneshwara Reddy
 
E

Eduardo

Hi,
Assuming that your words will be in cell A2 and you want the result in cells
B2 in B2 enter

=if(A2="Spot",4,if(A2="Renewal API","Renewals","Others"))

if this helps please click yes thanks
 
I

Igneshwara reddy

Hi ,

Thanks for your help.

The words which I have to find will be in a sentence format as below
"SPOT Order 210706 Submitted for Taylor, Michael for customer (GSC) QUANTUM"

The formula should find the words and get me the desired answers as I have
mentioned below.

Regards,
Igneshwara Reddy
 
J

Jacob Skaria

Try the below

=IF(ISERROR(SEARCH("spot",A1)),IF(ISERROR(SEARCH("Renewal
API",A1)),"Others","Renewals"),4)

If this post helps click Yes
 
E

Eduardo

Hi,
now is clear use

=IF(ISNUMBER(SEARCH("Spot",A2)),4,IF(ISNUMBER(SEARCH("Renewal
API",A2)),"Renewal","Others"))
 
S

Stefi

=IF(ISNUMBER(SEARCH("spot",A1)),4,IF(ISNUMBER(SEARCH("Renewal
API",A1)),"Renewals","others"))

Regards,
Stefi

„Igneshwara reddy†ezt írta:
 
S

Shane Devenshire

Hi,

Suppose you set up a little lookup table like:

Others
Renewal API Renewals
Spot 4

in cells A1:B3, and suppose that the entry you want to check is in A5 then
you can use the following array formula:

=INDEX(B1:B3,MAX(MATCH(1,--ISNUMBER(SEARCH(A1:A3,A5)),)))

as an array it must be entered by pressing Shift+Ctrl+Enter, not Enter.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top