Getting a store chain's name from a branch

[ScatmanKyle]

Let's say I have the store column below. I'm trying to make a formula that will scrub the column and return an output with just the type of store as shown in the "Desired Output" column. Because the amount of words before/after the type varies, I couldn't use the mid function to look for a set number of spaces. I was trying a nested IF statement using the SEARCH and ISNUMBER function, but currently there are 11 different outputs and Excel won't allow that level of nesting.

Store	Desired Output
CJ's CVS 3824	CVS
Ohio's Best Walmart FC312	Walmart
Frederico Sobeys RJ123	Sobeys
Dustin Walmart 7682 R2S	Walmart

<tbody>
</tbody>

Is there another way of working with formulas that I'm missing? Is it possible to use arrays with if statements?

Thanks in advance!

 

[Joe4]

You haven't considered stores with multiple word names, like "Best Buy", "Food Lion", etc.
This is going to be very difficult, because there don't seem to be any set rules to program by.
If you cannot define/explain the rules/logic in English, then you really cannot program something.

This issue happens a lot with names two, where people could have two words as a first name, or as a last name, or both.
Typically, the best that you can do is to come up with a rule/formula that will work in MOST situations, and manually adjust the rest (meaning someone needs to review them all).

 

[ScatmanKyle]

You haven't considered stores with multiple word names, like "Best Buy", "Food Lion", etc.
This is going to be very difficult, because there don't seem to be any set rules to program by.
If you cannot define/explain the rules/logic in English, then you really cannot program something.

This issue happens a lot with names two, where people could have two words as a first name, or as a last name, or both.
Typically, the best that you can do is to come up with a rule/formula that will work in MOST situations, and manually adjust the rest (meaning someone needs to review them all).

For stores like "Best Buy" and "Food Lion", I'd use one of the words that wouldn't show up in the others i.e. "Best" or "Lion" as my lookup. Like for "7 Eleven", I use "Eleven".

 

[Joe4]

For stores like "Best Buy" and "Food Lion", I'd use one of the words that wouldn't show up in the others i.e. "Best" or "Lion" as my lookup. Like for "7 Eleven", I use "Eleven".

I am a bit confused...
I thought you were talking about scrubbing data. Where do "lookups" come into play?
Can you please explain exactly what you are trying to do in more detail?
Are you dealing with a finite set of possibilities for the final scrubbed list?
Does that list of possibilities exist someone (i.e. stored somewhere in your file)?

 

[ScatmanKyle]

I am a bit confused...
I thought you were talking about scrubbing data. Where do "lookups" come into play?
Can you please explain exactly what you are trying to do in more detail?
Are you dealing with a finite set of possibilities for the final scrubbed list?
Does that list of possibilities exist someone (i.e. stored somewhere in your file)?

Apologies, when I mean lookup, I'm referring to my other attempts at trying. Like when using a nested IF statement, it would look something like =IF(ISNUMBER(SEARCH("Lion",A2)),"Food Lion",IF(ISNUMBER(SEARCH("Best"),"Best Buy" etc etc.

 

[Joe4]

Like when using a nested IF statement, it would look something like =IF(ISNUMBER(SEARCH("Lion",A2)),"Food Lion",IF(ISNUMBER(SEARCH("Best"),"Best Buy" etc etc.

A formula like that would only work if you had a known existing list with which you are working from/checking.
Do you have such a thing? If you have a set list of names that you are looking for within your list of unscrubbed names, we can probably work with that.