Searching cell string for "abc", then...

[kennyb]

Hi,

I am using the following formula [ =IF(ISERROR(SEARCH("*LCD*",F2,1)),"","LCD") ] to search for some characters in a string in column F. It then places the "LCD" characters in G2 cell if the string is contained in the cell.

The formula works great, but I have 9 other character options/combinations I also want to search from F:F and really don't want to have 9 columns of formulas to do this.

If I have a table on another sheet named "Options", am I able to look up vs that table to get the character strings I am looking for, search for those, and put the result in cell G2...Gn?
For instance:


[ =IF(ISERROR(SEARCH("[[all values of Table=Options]]",F2,1)),"","[[all values of Table=Options]]") ]

 

[T. Valko]

Hi,

I am using the following formula [ =IF(ISERROR(SEARCH("*LCD*",F2,1)),"","LCD") ] to search for some characters in a string in column F. It then places the "LCD" characters in G2 cell if the string is contained in the cell.

The formula works great, but I have 9 other character options/combinations I also want to search from F:F and really don't want to have 9 columns of formulas to do this.

If I have a table on another sheet named "Options", am I able to look up vs that table to get the character strings I am looking for, search for those, and put the result in cell G2...Gn?
For instance:


[ =IF(ISERROR(SEARCH("[[all values of Table=Options]]",F2,1)),"","[[all values of Table=Options]]") ]

Can you show us about 5-10 of the strings from column F and some of your "keywords".

And let us know what results you expect.

 

[Andrew Fergus]

Hello

Check out this thread:
http://www.mrexcel.com/forum/showthread.php?p=1597483

I believe the problem in that thread is almost identical to yours and it was issued as a challenge a couple of years ago by MrExcel. There are a lot of methods posted in that thread and it depends on what output you want to see. I recommend you check out the thread, try a couple of solutions and see how you get on.

[edit] Here is the definition of the problem : http://www.mrexcel.com/pc18.shtml
Even though it uses a lookup table to return a value from the next column, you are essentially wanting to return the value from the 1st column of the lookup table.

Andrew

 

[kennyb]

Some examples.

F column examples

/ms34/JDFS/FCGF/JAPAN/OTC/
/ms34/BANKING/BRAZIL/EMERGINGMKTS/
/ms34/JDFW/INTERNAL/AUSTRALIA/OPS_EQUITY/
/ms34/IMS/FRANCE/OPERATIONS/EQUITY/

Some keywords
FCGF
OPS_EQUITY
IMS

 

[Andrew Fergus]

Ok. How about this solution courtesy of Barry:
=LOOKUP(2^15,SEARCH(N$2:N$4,F2),N$2:N$4)

where F2 is the string you are searching, and the range N2:N4 contains the values you want to look for.

Andrew

 

[kennyb]

Barry...

I never knew you, but you are a legend. Worked perfectly.

Many thanks Andrew. I am glad you clarified a little bit.
I was a little lost with the first links you sent... Nice to be helped out of a bind by a fellow Kiwi.

Cheers all.