Conditional Formatting Rule to insert text into a blank cell if another cell contains text

[jcr964]

Please forgive me if this is a duplicate post. I wasn't sure what to search for to see if this has been answered in the past.

I have a spreadsheet that contains contact information and website URL for several thousand contractor companies. Header columns are: (A through I) "Company Name, Address, City, State, Zip Code, Phone, Email, and Website" I inserted a new blank column before "Company Name" and called it "Category" I would like to be able to categorize each company by the type of service that it provides.

I would like to learn how to code a Conditional Format Rule or function that would add text to column "A" cells if the company name (Column B) contains text that I specify.

Example: If Company Name in cell B1 contains "Roofing", then "Roofing" would be inserted into cell A1 in the "Category" column. Or if cell B457 contains "plumb" (partial match for plumber), then "Plumber" would be inserted in to A457.

Any assistance with this will be greatly appreciated.

Thanks in advance,

Jack

[Pete_UK]

You can't do what you want with conditional formatting - that just changes some of the formatting in a cell depending on some condition. What you want is a conditional formula. You can use this formula in A1:

=IF(COUNTIF(B1,"*Roofing*"),"Roofing",IF(COUNTIF(B1,"*plumb*"),"Plumber","none"))

where the asterisks are the wildcard character. You can add other terms instead of "none", in a similar style.

Hope this helps.

Pete

[jcr964]

Pete,

This will work. Thanks.

But I can foresee the amount of data in column A becoming very large and hard to manage by the time I add other categories.

Would it be possible to keep the formula in Column A short by looking up the "IF(COUNTIF" value and Category name from lists in 2 predefined columns on a separate worksheet?

Something like: =IF(COUNTIF(B1,"{column A cell range on separate worksheet}"),"{column B cell range on separate worksheet}","none")). VLookup maybe?

Example: On worksheet 2, column A will contain search strings (A1=*roof*, A2=*plumb*, A3=*heat*) and column B would have category names (B1=Roofing, B2=Plumber, B3=HVAC). Lets say that if any part of cell B1 on worksheet 1 was to match cell A3 (*heat*) on worksheet 2, then cell A1 on worksheet 1 would equal whatever value is in cell B3 (HVAC).

This would allow me to keep the formula in all column A cells consistent and short and allow me to add more search strings & categories easily.

This makes sense in my mind. I hope you are able to understand it.

Thanks,

Jack

[FDibbins]

If you can construct a table (1 column?) with a list of all the names you have, you could probably use INDEX/MATCH to pull in what you want.

Will the referenced column (B?) always just contain that 1 work, or will it be part of a larger test string?

[jcr964]

The following works, and I am thankful that it was shared, but it would be hard to manage when I add all of the construction categories to the formula and would be a lot of duplicated data in all of the cells in column A.

=IF(COUNTIF(B1,"*roof*"),"Roofing",IF(COUNTIF(B1,"*plumb*"),"Plumber","none"))

--

The VLookup function below also works if the Company Name in cell "B2" is an exact match.

=VLOOKUP(B2,Categories!A1:B5,2,0)

--

Is it possible to combine the 2 functions that would do partial match of text in column "B"?

I would like to be able to do partial matches because some companies use variations of a category name like "JJ Roofing" or "LA Roofers". On a 2nd worksheet named "Categories", I have 2 columns; column "A" contains partial names of categories with wild cards and column "B" contains the actual proper name of the categories.

I really appreciate everyone's input.

Jack

[bebo021999]

Assuming sheet Catergories cell A1:B5 contain criteria table

In A2 current sheet:

Please Login or Register  to view this content.

P/S: Cell A1 sheet cartergories contains criteria like this: "roof" (without "*")

[jcr964]

When I enter your formula and press enter, it wants to open a file. The title bar has, "Update Values: Categories". I don't know what file it would be looking for. Am I still doing something wrong?

Please assist,

Jack