Getting a store chain's name from a branch

ScatmanKyle

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

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!
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,727
Office Version
365
Platform
Windows
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

Board Regular
Joined
Oct 26, 2015
Messages
52
Office Version
365
Platform
Windows
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
Joined
Aug 1, 2002
Messages
53,727
Office Version
365
Platform
Windows
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

Board Regular
Joined
Oct 26, 2015
Messages
52
Office Version
365
Platform
Windows
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
Joined
Aug 1, 2002
Messages
53,727
Office Version
365
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,730
Messages
5,488,540
Members
407,643
Latest member
samerf86

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top