Getting a store chain's name from a branch

ScatmanKyle

New Member
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.

StoreDesired Output
CJ's CVS 3824CVS
Ohio's Best Walmart FC312Walmart
Frederico Sobeys RJ123Sobeys
Dustin Walmart 7682 R2SWalmart

<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

MrExcel MVP, Junior Admin
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).
 
Last edited:

ScatmanKyle

New Member
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

MrExcel MVP, Junior Admin
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

New Member
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

MrExcel MVP, Junior Admin
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.
 

Some videos you may like

This Week's Hot Topics

Top