Re: Formula to Find Substring in Cell Containing Text String

Laurent:

Very nice formula.  But, I do not understand the second one,
that you 
indicated was case-sensitive.  I seem to get inconsistent
results.

ANybody else play around with it ?    Thanks


>From: Laurent <ltwrightBLUEYONDER.CO.UK>
>Reply-To: MS Excel General Q & A List
<EXCEL-GPEACH.EASE.LSOFT.COM>
>To: EXCEL-GPEACH.EASE.LSOFT.COM
>Subject: Re: Formula to Find Substring in Cell
Containing Text String
>Date: Mon, 19 Mar 2007 20:44:41 -0000
>
>As ever, my pleasure!
>
>-----Original Message-----
>From: MS Excel General Q & A List
[mailto:EXCEL-GPEACH.EASE.LSOFT.COM] On
>Behalf Of James Moffitt
>Sent: 19 March 2007 20:27
>To: EXCEL-GPEACH.EASE.LSOFT.COM
>Subject: Re: Formula to Find Substring in Cell
Containing Text String
>
>Perfect, Laurent. Thank you very much.
>
>-----Original Message-----
>From: MS Excel General Q & A List
[mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
>On Behalf Of Laurent
>Sent: Monday, March 19, 2007 3:05 PM
>To: EXCEL-GPEACH.EASE.LSOFT.COM
>Subject: Re: Formula to Find Substring in Cell
Containing Text String
>
>Try (non case sensitive)
>=IF(ISERROR(SEARCH("RECRUIT",E2)),""
,"Recruit")
>Or
>(Case sensitive)
>=IF(ISERROR(FIND("RECRUIT",UPPER(E2))),"&
quot;,"Recruit")
>
>
>L
>
>-----Original Message-----
>From: MS Excel General Q & A List
[mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
>On Behalf Of MF
>Sent: 19 March 2007 19:08
>To: EXCEL-GPEACH.EASE.LSOFT.COM
>Subject: Re: Formula to Find Substring in Cell
Containing Text String
>
>Give this a try:
>
>=IF(IF(ISERROR(SEARCH(SearchArg,Sample,1)),0,SEARCH(Sear
chArg,Sample,1))
> >0,"
>recruit","")
>
>MF
>
>
>
>
>
>______________________________
>At 03:25 PM 19/03/2007, you wrote:
> >I'd like cell D2 to display the word recruit if the
string in E2
> >contains the word recruit or recruiting or
recruiter or any other
> >variant of the word recruit.
> >
> >If the string in E2 does not include a variant of
the word recruit I'd
> >like D2 to be blank.
> >
> >How could this be done?
> >
> >Thanks.
> >
>
>--------------------------------------------------------
---------------
> >--- The EXCEL-G list is hosted on a Windows NT(TM)
machine running
> >L-Soft international's LISTSERV(R) software.  For
subscription/signoff
> >info and archives, see
> >htt
p://peach.ease.lsoft.com/archives/excel-g.html .
> >                              COPYRIGHT INFO:
> >http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>
>--------------------------------------------------------
----------------
>--
>The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
>international's LISTSERV(R) software.  For
subscription/signoff info and
>archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
>                              COPYRIGHT INFO:
>http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>
>--------------------------------------------------------
----------------
>--
>The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
>international's LISTSERV(R) software.  For
subscription/signoff info and
>archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
>                              COPYRIGHT INFO:
>http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>
>--------------------------------------------------------
------------------
>The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
>international's LISTSERV(R) software.  For
subscription/signoff info
>and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
>                              COPYRIGHT INFO:
>http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>
>--------------------------------------------------------
------------------
>The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
>international's LISTSERV(R) software.  For
subscription/signoff info
>and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
>                              COPYRIGHT INFO:
>http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G

____________________________________________________________
_____
Mortgage refinance is hot 1) Rates near 30-yr lows 2) Good
credit get 
intro-rate 4.625%* 
https://w
ww2.nextag.com/goto.jsp?product=100000035&url=%2fst.jsp&
amp;tm=y&search=mortgage_text_links_88_h2a5f&s=4056&
amp;p=5117&disc=y&vers=743

------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software.  For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G