Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 68
Default finding words in the cell

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,276
Default finding words in the cell

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

"Igneshwara reddy" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 68
Default finding words in the cell

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

"Eduardo" wrote:

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

"Igneshwara reddy" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default finding words in the cell

Try the below

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

If this post helps click Yes
---------------
Jacob Skaria


"Igneshwara reddy" wrote:

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

"Eduardo" wrote:

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

"Igneshwara reddy" wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,276
Default finding words in the cell

Hi,
now is clear use

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

"Igneshwara reddy" wrote:

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

"Eduardo" wrote:

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

"Igneshwara reddy" wrote:

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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,646
Default finding words in the cell

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

Regards,
Stefi

Igneshwara reddy ezt *rta:

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

"Eduardo" wrote:

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

"Igneshwara reddy" wrote:

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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default finding words in the cell

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Igneshwara reddy" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding 2 or more words in excel golden Excel Discussion (Misc queries) 7 August 21st 09 04:38 PM
Finding Repeated Words Excel Worksheet Functions 4 June 9th 09 10:54 PM
Finding Arabic words in lists in Excel 2007 علاء Excel Discussion (Misc queries) 0 October 9th 08 06:20 AM
problem in finding words into cells of a column Claudio P. Excel Discussion (Misc queries) 3 January 25th 06 11:10 PM
Finding Words In Excel Spreadsheets & Word Documents rbonner79416 Excel Discussion (Misc queries) 0 March 18th 05 05:25 PM


All times are GMT +1. The time now is 11:56 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"