Getting a store chain's name from a branch

ScatmanKyle

Board Regular
Joined
Oct 26, 2015
Messages
65
Office Version
  1. 365
Platform
  1. Windows
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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Desired Output[/TD]
[/TR]
[TR]
[TD]CJ's CVS 3824[/TD]
[TD]CVS[/TD]
[/TR]
[TR]
[TD]Ohio's Best Walmart FC312[/TD]
[TD]Walmart[/TD]
[/TR]
[TR]
[TD]Frederico Sobeys RJ123[/TD]
[TD]Sobeys[/TD]
[/TR]
[TR]
[TD]Dustin Walmart 7682 R2S[/TD]
[TD]Walmart[/TD]
[/TR]
</tbody>[/TABLE]

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

Thanks in advance!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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:
Upvote 0
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".
 
Upvote 0
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)?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top