Search for Multiple Items and Display Result of Particular Word

[Ajay_tippu]

Hi All,

I Want to fetch a data as below.

ITEM RESULT
M&C_ES_F_55+_Phase1 <55>
M&C_BR_F_18-34_Phase1 <18-34>
M&C_ES_F_35-54_Phase1 <35-54>

Likewise there are many line items, wherein i want the Age should be searched in ITEM column and should be displayed in the RESULT column.

* I don't want to Use text to column method, is it possible to use IF condition along with Search formula.


Thanks in advance.

Regards,
Ajay

[newdoverman]

Enter this in B2 and copy down:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"F_","/"),"_Phase",REPT(" ",100)),SEARCH("/",SUBSTITUTE(SUBSTITUTE(A2,"F_","/"),"_Phase",REPT(" ",100)),1)+1,20))

The result will be:

55+
18-34
35-54

If you need the "<>" then this should do it:

=TRIM("<"&(MID(SUBSTITUTE(SUBSTITUTE(A2,"F_","/"),"_Phase",REPT(" ",100)),SEARCH("/",SUBSTITUTE(SUBSTITUTE(A2,"F_","/"),"_Phase",REPT(" ",100)),1)+1,20))&">")

To get rid of the + sign this will do it.

=TRIM("<"&(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"F_","/"),"_Phase",REPT(" ",100)),"+",""),SEARCH("/",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"F_","/"),"_Phase",REPT(" ",100)),"+",""),1)+1,20))&">")

The result is:

<55 >
<18-34 >
<35-54 >