See if string contains any of the keywords on a table and return nickname of that keyword

PROJECT IDENTIFICATION .xlsm


Hi everyone,

So I have been searching everywhere and cannot find a VBA code to salvage, that,

would look in a string (Column H of sheet 1 ) see if it finds a keyword (from (ProjectID) Column B of sheet 2) within that string and return on Column R of sheet1 the matching ProjectID's project name written on Column C of Sheet 2 for each row of sheet 1



The project ID Table is on sheet 2

Column B has the Project ID and Column C has the Project name
Column A has a sorting alphanumerical code to allow the Most Unique Project ID to be at the bottom to help avoid false positives.



examples of string in (column H of sheet 1) =
USER,JOB TYPE,PROJECT1-010,BATCH 1026 >>>>>>>>> "should return PROJECT 1"
USER,JOB TYPE,PROJECT2-020,BB.BATCH 1026 >>>>>>>>> "Should return BB"
USER,JOB TYPE,PROJECT3-030,CC,BATCH 1026 >>>>>>>>> "Should return CC"

example of project table (columns B and C of Sheet 2) =

(B)PROJECT ID--------(C)PROJECT NAME
,PROJECT1-010--------PROJECT 1
,PROJECT2-020,BB--------BB
,PROJECT3-030,CC--------CC



SO if Sheet1's column (H) string CONTAINS (A PROJECT ID ) THEN (R) = PROJECT NAME(Column B

I would later try to add two more conditions:


IF column (N) CONTAINS the word "EXTRA" THEN (R) = EXTRA

IF column (H) CONTAINS NON OF THE PROJECTS AND IS NOT EXTRA THEN(R)= REGULAR



This macro needs to run for as many rows that are in sheet 1 that the value of Column A's cell 's formula is not blank. and start from the top of sheet1 when run and all the way down until Column A's Formula's Result is BLANK.

The I Idea is to be able to add any new Project ID and have it recognized somewhere in Column H's String and populate the proper project name.


Any help would be appropriated

I can send u the sheets if u send me your email.

I hope I was as clear as possible
ask me if I forgot to specify something

https://onedrive.live.com/redir?resi...D409CDD1%21893 is the link to download the Sheet

once open please click on File/ Save as to save locally

private message me to have my email to send it back


I have also attached a copy to the original post .


Thank you

Thank you Sir, I really appreciate the time you put into this

I wish I had your skills on this ,

2 Questions:

can the code be completely independent of commas ?

arrSplit = Split(rCell.Value, ",")
str = arrSplit(2) seems to be looking for Comma in the String... x2 sadly the commas are not necessarily in that order to have the project id written after the second comma,

Is there a way to have this work without taking in consideration the position of commas ?

there always will be a comma before the Project ID but there could be more or less Commas before it
i could add the comma before the project id in the table if that helps but the code has to be able to find the project id regardless of it's position in the string.



also Where would you Add a stop to the calculations so that it runs and all the way down on sheet 1 until Column A's Formula's Result is BLANK.