isolating specified characters

[Rambergs]

Can anyone tell me how I can isolate specific text from a text string...
there's a catch though ..

I am trying to 'flag' specific commercial shipping information that has been entered onto an Excel sheet : ie.

opt 6 mos - which means, literally, '6 month option'

I want to isolate the 'opt' part and using something like
=IF((SEARCH("opt",A10,1))>0,"EXT","")

..is only half the solution in that it WILL isolate the 'opt' but as I an using this in a whole column of similar entries that may/may not have 'opt' included, when there is NO 'opt' the SEARCH returns #VALUE!
I want it to return either 'OPT' or a blank field. That's the first part of this query.

The second part is that there is another condition that could arise (and I want to isolate also) - example is :

ext 12 Mos - which means '12 months excepted'

I need to isolate the 'ext' and display it as EXT (as in the SEARCH method above)

Ideally I would want to cover both possibilities in a single combination of functions that would work alonf the lines of the following logic :
IF <search for 'opt'> TRUE then display OPT,
IF <search for 'ext'> TRUE then display EXT,
otherwise display <blank>

Any advice would be appreciated thanks.

[MickG]

Hi, You could try this UDF:-
Right click sheet tab, Click View code, Select Insert, Select Module, Paste in VB Editor window.

Please Login or Register  to view this content.

Regards Mick

[Domenic]

Try...

=IF(ISNUMBER(SEARCH("opt",A2)),"OPT",IF(ISNUMBER(SEARCH("ext",A2)),"EXT",""))

Hope this helps!

[Dunc3142]

This works too
=IF(ISERROR(FIND("opt",A14)=3),"","opt")&IF(ISERROR(FIND("ext",A14)=3),"","ext")

As in sheet attached