excel function on how to retrive piece of text from range of cells

[Rohit2500]

Hi,

I have some range of data in column A like

text_CPC_text
text_shopping_text
text_CPL_text

I want to extract CPC in column B wherever it is mentioned in columnn A
and i want to extract shopping in column B wherever it is mentioned in columnA.

I am trying IF(A:A="CPC","CPC", IF(A:A="shopping",Shopping")) but it is not working.

Please help me with correct function.

Regards,
Rohit

[kvsrinivasamurthy]

Try this

Please Login or Register  to view this content.

[arlu1201]

Try this formula

Please Login or Register  to view this content.

[Rohit2500]

Try this

Please Login or Register  to view this content.

Hi Srinivasamurthy,

Thankyou for your response, but it is not working, kindly suggest some other code

[arlu1201]

Did you try the formula i provided you?

[Rohit2500]

Yes Arul,

I have tried your formula but it shows error msg box.

[Rohit2500]

I have tried using =if(search("CPL",A1)=1,"CPL", if(search("shopping",A1)=1,"Shopping")"")

but it again shows error in msg box, may be i am not writing function correctly.

Please suggest how to use multiple search function.

[Teethless mama]

I have tried using =if(search("CPL",A1)=1,"CPL", if(search("shopping",A1)=1,"Shopping")"")

but it again shows error in msg box, may be i am not writing function correctly.

Please suggest how to use multiple search function.

Try it like this:

=LOOKUP("zzz",CHOOSE({1,2},"",LOOKUP(1E99,SEARCH({"CPL","shopping"},A1),{"CPL","shopping"})))

[azumi]

=SUBSTITUTE(SUBSTITUTE(A1;LEFT(A1;FIND("_";A1;1));"");RIGHT(SUBSTITUTE(A1;LEFT(A1;FIND("_";A1;1));"");FIND("_";A1;1));"")

It Work bro, what MS Excel Version do you use?

Put your data in A1 first bro --> text_CPC_text

[Rohit2500]

Hi Azumi,

It is showing error even if i put data in A1.

[Rohit2500]

Hi Teethless mama,

Your code is working a bit but i didnt unde it is showing zero result (without any error), also pls tell me have u taken "zzz" and lookup(1E99

[Rohit2500]

Friends,

I have tried this code and finally its working. You can refer below code if required.

=IF(SUM(COUNTIF(A2,{"*CPL*"}))>0,"CPL",IF(SUM(COUNTIF(A2,{"*shopping*"}))>0,"SHOP","CPM"))