Reducing a formula

Could this formula be cut down with the Choose function? The state abbreviations appear nowhere on the spreadsheet. To the left of the formula is either an 8 digit number or a 9 digit number. If it is 8 I am only concerned with the first number. If it is 9 digits I want the first two numbers.

=IF(LEN(D2)=8,(IF((LEFT(D2,"01")="1"),"AL",(IF((LEFT(D2,"01")="2"),"AK",(IF((LEFT(D2,"01")="3"),"AZ",(IF((LEFT(D2,"01")="4"),"AR",(IF((LEFT(D2,"01")="5"),"CA",IF((LEFT(D2,"01")="6"),"CO",(IF((LEFT(D2,"01")="7"),"CT",(IF((LEFT(D2,"01")="8"),"DE",(IF((LEFT(D2,"01")="9"),"DC"))))))))))))))))),(IF((LEFT(D2,"02")="10"),"FL",IF((LEFT(D2,"02")="11"),"GA",IF((LEFT(D2,"02")="12"),"HI",IF((LEFT(D2,"02")="13"),"ID",IF((LEFT(D2,"02")="14"),"IL",IF((LEFT(D2,"02")="15"),"IN",IF((LEFT(D2,"02")="16"),"IA",IF((LEFT(D2,"02")="17"),"KS",IF((LEFT(D2,"02")="18"),"KY",IF((LEFT(D2,"02")="19"),"LA",IF((LEFT(D2,"02")="20"),"ME",IF((LEFT(D2,"02")="21"),"MD",IF((LEFT(D2,"02")="22"),"MA",IF((LEFT(D2,"02")="23"),"MI",IF((LEFT(D2,"02")="24"),"MN",IF((LEFT(D2,"02")="27"),"MT",IF((LEFT(D2,"02")="25"),"MS",IF((LEFT(D2,"02")="26"),"MO",IF((LEFT(D2,"02")="28"),"NE",IF((LEFT(D2,"02")="29"),"NV",IF((LEFT(D2,"02")="30"),"NH",IF((LEFT(D2,"02")="31"),"NJ",IF((LEFT(D2,"02")="32"),"NM",IF((LEFT(D2,"02")="33"),"NY",IF((LEFT(D2,"02")="34"),"NC",IF((LEFT(D2,"02")="35"),"ND",IF((LEFT(D2,"02")="36"),"OH",IF((LEFT(D2,"02")="37"),"OK",IF((LEFT(D2,"02")="38"),"OR",IF((LEFT(D2,"02")="39"),"PA",IF((LEFT(D2,"02")="40"),"RI",IF((LEFT(D2,"02")="41"),"SC",IF((LEFT(D2,"02")="42"),"SD",IF((LEFT(D2,"02")="43"),"TN",IF((LEFT(D2,"02")="44"),"TX",IF((LEFT(D2,"02")="45"),"UT",IF((LEFT(D2,"02")="46"),"VT",IF((LEFT(D2,"02")="47"),"VA",IF((LEFT(D2,"02")="48"),"WA",IF((LEFT(D2,"02")="49"),"WV",IF((LEFT(D2,"02")="50"),"WI",IF((LEFT(D2,"02")="51"),"WY",IF((LEFT(D2,"02")="60"),"AS",IF((LEFT(D2,"02")="61"),"FM",IF((LEFT(D2,"02")="66"),"GU",IF((LEFT(D2,"02")="67"),"MH",IF((LEFT(D2,"02")="68"),"MP",IF((LEFT(D2,"02")="69"),"PW",IF((LEFT(D2,"02")="70"),"CN",IF((LEFT(D2,"02")="72"),"PR",IF((LEFT(D2,"02")="75"),"TT",IF((LEFT(D2,"02")="78"),"VI",))))))))))))))))))))))))))))))))))))))))))))))))))))))



Moderator's Note: This post was moved from this thread as per the Forum Rules. Please take a moment to read the rules, you are expected to abide them. Thanks.

Can't you create a lookup table with your numbers in the first column and the state abbreviations in the seconds, then use VLOOKUP to just lookup the value in the table?

Lookup tables are extremely practical and easier to read than formulas like the one created above. With the work done once of creating the reference table, your formulas would then be remarkably short.

Can you provide several examples of the text strings that would be in D2?

If you insist to have thsi in one formula try something on these lines..

=IF(LEN(D2)=8,choose(LEFT(D2,1)*1,"AL","AK","AZ","AR",......................),choose(left(d2,2)*1,"FL","GH","AI",...................))

The CHOOSE function is not even close to being as long.

All you need to do is fill in the expected values in the dotted spaces in my above formula with the 2 letter abbr.

Please Login or Register  to view this content.

P.S. Having a refernce table works much better

Did you try my formula in Post # 8? Does it give desired results?

As a comparison..

Your original gargantuan formula - 1855 characters
Suggested formula - 386 characters

Originally Posted by Jaypelt

....our system gets bogged down in high traffic periods (when i would want to be using the formula), and referencing a table could lock my system up...

People get the oddest ideas. There is no possible way a reference table would be the cause of your system locking, in fact the exact opposite is true. Your existing formula can do upwards of 60+ calculcations in a single cell. Creating a lookup table means the needed value is found every time in a single calculation... sixty fewer calculations per cell.

You've experienced something in the past while using Excel that has left you with false impressions of what really happened. The lookup table IS the correct solution for this 'exercise'.

Ace's Choose suggestion is an excellent next step. I fear you are tossing out the correct solutions given to you for no reason, and not even trying them. That is unfortunate.

You don't even need to have the IF, you can use a single CHOOSE function like this

=CHOOSE(LEFT(D2,LEN(D2)-7)+0,"AL".....etc. etc.)

although I also think that a 2 column table, as JB suggested, would be the optimal approach (table can be on the same worksheet or on another worksheet in the same workbook so you wouldn't be opening anything that's not already open), name your table States and then this is the whole formula

=VLOOKUP(LEFT(D2,LEN(D2)-7)+0,States,2,0)

Glad you got what you needed from Ace.

BTW, I would never recommend putting the reference table in a separate workbook, rather it, too, would be in the same workbook where the formulas are being used.


If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

All the commas in the formula is ...

quick, easy, and precise

...


A Lookup table is without a doubt the way to go.

An alternative to DLLs'
CHOOSE(LEFT(D2,LEN(D2)-7)+0
might be ...

Formula:

Please Login or Register  to view this content.

Originally Posted by Marcol

INT(D2/10^7)

I like that.....

......I'd be wary of using LOOKUP, though, Marcol. Perhaps it isn't possible but if D2 starts with 65 (out of 9 digits) then you'll get a "false match" with 61 as the next lowest number on the list. VLOOKUP (with FALSE or zero as 4th argument) will give you #N/A and the CHOOSE approach would give you zero, both of which should alert you to the problem....