Getting a store chain's name from a branch
Results 1 to 6 of 6

Thread: Getting a store chain's name from a branch
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2015
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Getting a store chain's name from a branch

    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

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

    Thanks in advance!

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,383
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Getting a store chain's name from a branch

    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 by Joe4; Aug 13th, 2019 at 01:00 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Oct 2015
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Getting a store chain's name from a branch

    Quote Originally Posted by Joe4 View Post
    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".

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,383
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Getting a store chain's name from a branch

    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)?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    Oct 2015
    Posts
    48
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Getting a store chain's name from a branch

    Quote Originally Posted by Joe4 View Post
    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.

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,383
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Getting a store chain's name from a branch

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •