if cell contains any of the entries in a column, insert specific text in neighboring cell

Hi,

I'd need to scan a cell in (Sheet1) for the text in a specific column (A) of another sheet (Sheet2). Like a VLOOKUP, if the cell searched (Sheet1!C2) contains the text in one cell of the column (Sheet2!A), I'd like to add the text in another column (Sheet2!B) to the cell containing the formula (Sheet1!C3). There may be multiple values in the cell (Sheet1!C2) so I need to append the additional text. Finally, I need to do this with a formula rather than a macro (I think...) so that I can change the text in Sheet1!C or in Sheet2B and have the worksheet update.

I'm not sure if that makes any sense. Here's an example: Sample.xlsx

I noticed that any text that I don't need to search is inside of parenthesis, and a semicolon separates each set of values in both the cell to search, and also in the cell that I want to add the string to.

I tried doing this myself, but quickly realized that this was more involved than a VLOOKUP and a FIND. I'm out of my depth (which admittedly doesn't take much). I'd really appreciate some help on this one.

It's getting a bit late here so I don't have time to give you a complete solution. However, I think you would find it easier to make use of a number of helper columns, which could be hidden once you have the final solution. For example, if you put this formula in D2 and copy it down:

=IF(C2="","",SUBSTITUTE(IF(ISNUMBER(SEARCH("(",C2)),REPLACE(C2,SEARCH("(",C2),SEARCH(")",C2)-SEARCH("(",C2)+1,""),C2)," ","")&";")

you would get this result:

H1;TRANS1;HEP1;
H1;IL1;FOUN1;
LEG1;

H1;HEP1;

TRANS1;NE1;IL1;

The formula has removed any text in brackets (assuming only one set of comments, as in your example) as well as removing any spaces, and has also added a semicolon to the end. Subsequent columns could then work on this column of data to extract individual codes and use VLOOKUP to translate them, and then those columns could be concatenated to give you the final result.

Hope this helps.

Pete

OK, so now I have several helper columns, each of which runs a find on the target cell for one of the different abbreviations that I'm looking for. If it finds it, the full text is added to that column. Essentially what I end up with is several columns, each with one of the full values that I need. Can someone help me figure out how to take this information and concatenate it in a new column, separated by semicolons? I suspect that it needs to be a conditional concatenate somehow, since I end up with a lot of unruly semicolons and spaces if I just have it concatenate each cell followed by a semicolon and a space. Here is an example of where things stand right now: Sample.xlsx

Again, one key to this is to make it so that it automatically updates the full text if someone removes or adds an abbreviation, or if someone changes one of the full values.

Thanks again for the input everyone!

-Rob

I agree with Marvin here - a macro would be a simpler solution. But, it you want to persevere with formulae you could put this formula in K2 of Sheet1:

=IF(D2="","",D2&"; ")&IF(E2="","",E2&"; ")&IF(F2="","",F2&"; ")&IF(G2="","",G2&"; ")&IF(H2="","",H2&"; ")&IF(I2="","",I2&"; ")&IF(J2="","",J2&"; ")

This will give you what you want, but there will be a semicolon at the end of the string. If you don't want this then you could use this formula in L2:

=IF(K2="","",LEFT(K2,LEN(K2)-2))

Hope this helps.

Pete

Hey Marvin,

I'm working on a different project where the VBA script you gave me is almost perfect! Thank you! I need to figure out how to remove all of the extra comments so that anything not in the list of abbreviations is omitted. Can you help?

Thanks,

Rob

Thanks Marvin,

Funny you should suggest a new post http://www.excelforum.com/excel-prog...-is-found.html.