As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options

Help with Excel Function: IF(ISNUMBER(FIND

SkyGheNeSkyGheNe Registered User regular
edited December 2010 in Help / Advice Forum
This is what I want to do:

In cell A1 is the phrase McDonalds - 300x250 - AZ
In Cell A2, there is McDonalds - 300x250 - AZCTNC

I need to create a function that will be able to tell the difference between the two and then spit out in a separate cell that the state for A1 is Arizona, and that the State for A2 is a cluster of states.

I started by using this:

=IF(ISNUMBER(find("AZ",$A1)),"Arizona",IF(ISNUMBER(Find("AZCTNC",$C6)),"Cluster",))

However, it looks at A2, sees the AZ and puts in Arizona. Is there a way of getting excel to spot the complete phrase and only the AZ within the contents of the entire cell?

SkyGheNe on

Posts

  • Options
    schussschuss Registered User regular
    edited December 2010
    The function you probably want is FIND.
    Format -
    Find(string to find, text to search, where in the string to start)

    Probably a combo of that and the RIGHT function will get you the various strings, which you can then chop up. It's a fucking terrible way to format data though.

    schuss on
  • Options
    LaOsLaOs SaskatoonRegistered User regular
    edited December 2010
    So, you want it to either say "Arizona" or "Cluster" depending if it's got AZ or AZ****? Like, you want it to spit out the single state or, if there's more than one state's initials, you want it to just say something like "cluster" or "multiple"?

    Is there a way you could use some other character to separate McDonalds and 300x250 or 300x250 and AZ? If you can, you can use MID to isolate the last characters--either one state's abbreviation or a cluster of states--and then check that result against a table of data, resulting in either the name of a single state or something like "cluster" for multiple states. If you have common clusters, you could indicate them as well.

    For example, if you are able to change the character separating the first two items (I've used ~ instead of -), you can have a formula that searches for the location within the phrase of - and returns whatever comes after that. I've gone further and run that result against a table that has state abbreviations and names so that it returns the full name if it's on the list. I've also included some common clusters that can be returned as well. And, if it's not a common cluster, a generic cluster label is returned.

    statenames.jpg

    The Phrase, State Abbr. and State Name columns are all just text that's been entered. The Results column has the actual formula. Which, by the way, looks like this:

    =IF(COUNTIF($C$2:$C$6,MID($A4,FIND("-",$A4,1)+2,(LEN($A4)-FIND("-",$A4,1))))<1,"Generic Cluster",INDEX($D$2:$D$6,MATCH(MID($A4,FIND("-",$A4,1)+2,(LEN($A4)-FIND("-",$A4,1))),$C$2:$C$6,0),1))

    Basically, there's an IF statement checking to see if the information at the end of the Phrase exists in our list of states and common clusters. If it doesn't exist, it will return the "Generic Cluster" result. However, if it does exist in the list, it will take the information from the end of the phrase and find the corresponding State Name from our list. The MID formula pulls out the end of each phrase for all this checking to happen.


    =IF(COUNTIF($C$2:$C$6,MID($A4,FIND("-",$A4,1)+2,(LEN($A4)-FIND("-",$A4,1))))<1,"Generic Cluster",INDEX($D$2:$D$6,MATCH(MID($A4,FIND("-",$A4,1)+2,(LEN($A4)-FIND("-",$A4,1))),$C$2:$C$6,0),1))

    This is the first IF statement, checking to see if the end of the phrase information appears in our list. It uses the MID formula to isolate the end bits of the phrase and then uses a COUNTIF to see how many times that isolated information appears in the table of State Abbr. and State Name bits. If the isolated information appears <1 times, it results in "Generic Cluster". However, if the isolated information appears at least 1 time in our table, the rest of the formula does its magic.


    =IF(COUNTIF($C$2:$C$6,MID($A4,FIND("-",$A4,1)+2,(LEN($A4)-FIND("-",$A4,1))))<1,"Generic Cluster",INDEX($D$2:$D$6,MATCH(MID($A4,FIND("-",$A4,1)+2,(LEN($A4)-FIND("-",$A4,1))),$C$2:$C$6,0),1))

    This bit of the formula finds where the isolated information from the phrase appears in our table of Abbreviations and Names, matching the indicated name with the isolated state information in the phrase. INDEX looks through the first column of our table to find the isolated information (that our MID formula grabbed). It starts by just looking at the Name column, because that's where it's going to find its results. It uses a MATCH formula to determine what row to look at in the name column. The MATCH formula uses the information from our MID formula to find on which row the isolated information sits (for example, CT is on row 2 in the array, and AZ is in row 1). MATCH only looks at the abbreviation column. When the MATCH information tells the INDEX formula what row to look in, it pulls out the corresponding State Name.

    The main workhorse in this whole formula is the MID formula. This will only work if you are able to differentiate between the first separating character and the second. The MID formula tries to find information from the middle of the phrase, starting at some point in there that we tell it to start looking at, and going for as many characters as we ask it to. FIND and LEN help us determine the last bits of information.


    =IF(COUNTIF($C$2:$C$6,MID($A4,FIND("-",$A4,1)+2,(LEN($A4)-FIND("-",$A4,1))))<1,"Generic Cluster",INDEX($D$2:$D$6,MATCH(MID($A4,FIND("-",$A4,1)+2,(LEN($A4)-FIND("-",$A4,1))),$C$2:$C$6,0),1))

    These are our MID formulas (they're exactly the same). We're telling the MID to look at the phrase, use FIND to locate the "-" character (it looks from left to right and would stop at the first instance of the character, hence our having to change the first separating character--you could change the second rather than the first and replace it here) by starting its search at the first character in the phrase, and then return a number of characters that is equal to the total number of characters in the phrase (LEN) minus the number of characters, from left to right, where the "-" character is found in the phrase (FIND). The first time we use FIND, we +2 to the location of the "-" to account for the character itself and the space to the right of it--we want the first character of the end information, not useless bits. We don't do that the second time, when we're subtracting the location of "-" from the total number of characters in the phrase.

    ==========

    This, of course, depends on you being able to change the separating characters to make them unique and on the setup of your data. The table of State Abbreviations (and common clusters) and State Names (and cluster names) can be located on a different worksheet, or even another Excel file.

    I'm heading home now but will be around a bit this evening. I'll also be back browsing from work tomorrow if you've got any questions. Hopefully it's not over your head. The reason your formula was returning Arizona for both phrases is because you were just telling it to look at the contents of the phrase and return Arizona if it included AZ at all. If the phrase was "Amazon - 300x250 - CT" it would still return Arizona, etc.

    [Edit]
    The other important bit (besides the unique separators) is that you have to use absolute referencing for the arrays in the COUNTIF, INDEX, and MATCH formulas, but can use relative referencing for the phrase location. You'd obviously have a much larger table of State Abbreviations and State Names, etc.

    LaOs on
Sign In or Register to comment.