Find Search function

[baggarwal]

Hi All:

In the current column is the data I pull. The second column extracts text from it. Can someone please show me a formula that I can put in the desired column that will produce the result desired.
Current Desired
0107-01 - TLEU_Dec 06 TLEU
0207-01 - 441ZZ1_TLEU_Feb 07 TLEU
36-0107-02 - VIJE_Dec 06 VIJE
0207-01 - 441ZZ1_TLEU_Feb 07 TLEU
0407-01 - TLEU_Svcs Fee TLEU
0207-02 - 441ZZ2_VIJE - Feb 07 VIJE
0307-01 - TLEU_Feb 07 TLEU
0307-02 - VIJE_Feb 07 VIJE
0407-02 - VIJE_Svcs Fee VIJE
0507-01 - TLEU_Apr 07 TLEU
0507-02 - VIJE_Apr 07 VIJE
0607-01 - TLEU_ZZ1 TLEU

Basically I want to extract VIJE and TLEU from the long text.

 

[R-D]

Provided your sample is inclusive of all possible formats, try this:

=IF(ISERROR(MID(A1, SEARCH(" - ",A1)+3, 1)*0),MID(A1,SEARCH(" - ",A1)+3,4),MID(A1,SEARCH("_",A1)+1,4))

Where A1 is the cell containing the text you want to search.

 

[baggarwal]

Can you please explain how you generated this formula and how each function embedded is put together to produce the end result.

 

[Domenic]

Try...

=LOOKUP(9.99999999999999E+307,FIND({" TLEU_"," VIJE_"}," "&A2&"_"),{"TLEU","VIJE"})

However, it looks like TLEU and VIJE will not occur anywhere else within the text string. If this is the case, the following would suffice...

=LOOKUP(9.99999999999999E+307,FIND({"TLEU","VIJE"},A2),{"TLEU","VIJE"})

Note that FIND is case-sensitive. If you don't want the formula to be case-sensitive, replace FIND with SEARCH.

Hope this helps!