IF Formula to Find Specific Text in a String & Categorize in Adjacent Column

[pebarry]

Hello!

I was wondering if anyone has an IF formula that would allow me to find a specific string of text in a cell and then add in a category in an adjacent cell based on a list of 10 different categories.

For example: i have over 1,500 rows of text strings that look like "Miramar - In Market - Online BSN - Nonbrand - Phrase - Spanish" and I wanted to search that text string to see if it contains the words "BSN" and "Nonbrand". If it does, I would want the phrase "BSN Nonbrand" returned in the cell adjacent to that phrase.

Anyway I can scale this so that the formula would search for 10 different word combos in each text string and then have a text value returned on the adjacent cell? Let me know if you need more details.

Thanks!!!

pebarry

 

[thisoldman]

Ten nested IFs is a maintenance nightmare. It's also hard to write it correctly.

The given text is in A1.

=REPT("BSN Nonbrand", COUNT(SEARCH("Nonbrand", A1) * SEARCH("BSN", A1)))

We search for both words—if both are found, we get two numbers and the multiplication of these two is also a number. If one or both words are not found, we get a #VALUE! error.

The COUNT function only counts numbers. If the multiplication returns a number, the count is 1. If the multiplication returns an error, the count is zero.

REPT prints the phrase, "BSN Nonbrand", COUNT times. REPT prints the phrase either either one or zero times.

We can append more searches using the concatenation operator, '&'. For example, if A1 contains both "HAL" and "Acme", we want to to return the phrase "HAL Acme".

=REPT("BSN Nonbrand", COUNT(SEARCH("Nonbrand", A1) * SEARCH("BSN", A1))) & REPT("HAL Acme", COUNT(SEARCH("HAL", A1) * SEARCH("Acme", A1)))

More searches can easily be added to the formula by tacking on more &s.

The way I have written the last formula doesn't guard against finding all four words. The formula will then return "BSN NonbrandHAL Acme".

 

[Aladin Akyurek]

Too little info to go on.

So we are looking pairs of words, like BSN Nonbrand, in strings like:

Miramar - In Market - Online BSN - Nonbrand - Phrase - Spanish

Does this mean that if BSN and Nonbrand do co-occur in a string the co-occurrence is always structured as BSN first then Nonbrand with a space or a hyphen or a * in between?

 

[Clyde76]

Hi Pebarry,

I imagine that you'll also have to do some data exploration regarding this. Following on from Aladin's comment, are you confident that when you get to 10 patterns, they'll be mutually exclusive? If you're not sure, you'll probably want to build it in a way that will allow you to easily identify that and go from there.

Here is a screenshot of something that may help you with that process while also working towards what you probably want to achieve in the end.

You can create each unique phrase that you want to search for in a row (i.e. BSN) and then use a simple search function to look for that string. Return a 1 if you find it, and a 0 if you don't. I've also included some simple error handling.

This keeps your formulas simple and transparent for troubleshooting.

Once you've identified all of your unique search strings, compiling them to determine if they meet the multiple criteria (i.e. BSN & Nonbrand) is simple. BSN return value * Nonbrand return value will give you a 1 if they meet both and a 0 if they don't. You can easily adjust to meet your needs.

Once you've done this, which should be super quick and easy, you'll be able to better determine a way forward and easily pull back your desired final return string. (e.g. =IF(E3=1, "BSN Nonbrand".....)

Hope this gets you started.