Forum Discussion

JimHarrison's avatar
JimHarrison
Qrew Champion
11 days ago
Solved

Looking for State abbreviation to full spelling formula

OR my state abbreviation breaks Quickbase filters. Does anyone have a formula to share? 

 

formulas
  • MarkShnier__You's avatar
    MarkShnier__You
    11 days ago

    .. and here is another one.  I forget offhand why I had to do the upper and lowercase thing but I think it had to do with inconsistent data entry when users typed in the state name and the capitalization was inconsistent.  But I see I did something different for the states which had two names.  I have a feeling I should have just use the two word name method for all the states.  

     

    var text StateRaw = Trim([Address: State/Region]);
    var text State = Upper(Left($StateRaw,1)) & Lower(NotLeft($StateRaw,1));

    If(
    $State="Alabama", "AL",
    $State="Alaska", "AK",
    $State="Arizona", "AZ",
    $State="Arkansas", "AR",
    $State="California", "CA",
    $State="Colorado", "CO",
    $State="Connecticut", "CT",
    $State="Delaware", "DE",
    Upper($State)="DISTRICT OF COLUMBIA", "DC",
    $State="Florida", "FL",
    $State="Georgia", "GA",
    $State="Hawaii", "HI",
    $State="Idaho", "ID",
    $State="Illinois", "IL",
    $State="Indiana", "IN",
    $State="Iowa", "IA",
    $State="Kansas", "KS",
    $State="Kentucky", "KY",
    $State="Louisiana", "LA",
    $State="Maine", "ME",
    $State="Maryland", "MD",
    $State="Massachusetts", "MA",
    $State="Michigan", "MI",
    $State="Minnesota", "MN",
    $State="Mississippi", "MS",
    $State="Missouri", "MO",
    $State="Montana", "MT",
    $State="Nebraska", "NE",
    $State="Nevada", "NV",
    Upper($State)="NEW HAMPSHIRE", "NH",
    Upper($State)="NEW JERSEY", "NJ",
    Upper($State)="NEW MEXICO", "NM",
    Upper($State)="NEW YORK", "NY",
    Upper($State)="NORTH CAROLINA", "NC",
    Upper($State)="NORTH DAKOTA", "ND",
    $State="Ohio", "OH",
    $State="Oklahoma", "OK",
    $State="Oregon", "OR",
    $State="Pennsylvania", "PA",
    $State="Rhode Island", "RI",
    Upper($State)="SOUTH CAROLINA", "SC",
    Upper($State)="SOUTH DAKOTA", "SD",
    $State="Tennessee", "TN",
    $State="Texas", "TX",
    $State="Utah", "UT",
    $State="Vermont", "VT",
    $State="Virginia", "VA",
    $State="Washington", "WA",
    Upper($State)="WEST VIRGINIA", "WV",
    $State="Wisconsin", "WI",
    $State="Wyoming", "WY", 
    UPPER($State))

  • JimHarrison's avatar
    JimHarrison
    11 days ago

    This works for the Dashboard filters. 

    var text StateAbbr = [State]; var text AL = SearchAndReplace($StateAbbr,"AL","Alabama"); var text AK = SearchAndReplace($AL,"AK","Alaska"); var text AZ = SearchAndReplace($AK,"AZ","Arizona"); var text AR = SearchAndReplace($AZ,"AR","Arkansas"); var text CA = SearchAndReplace($AR,"CA","California"); var text CO = SearchAndReplace($CA,"CO","Colorado"); var text CT = SearchAndReplace($CO,"CT","Connecticut"); var text DE = SearchAndReplace($CT,"DE","Delaware"); var text FL = SearchAndReplace($DE,"FL","Florida"); var text GA = SearchAndReplace($FL,"GA","Georgia"); var text HI = SearchAndReplace($GA,"HI","Hawaii"); var text ID = SearchAndReplace($HI,"ID","Idaho"); var text IL = SearchAndReplace($ID,"IL","Illinois"); var text IN = SearchAndReplace($IL,"IN","Indiana"); var text IA = SearchAndReplace($IN,"IA","Iowa"); var text KS = SearchAndReplace($IA,"KS","Kansas"); var text KY = SearchAndReplace($KS,"KY","Kentucky"); var text LA = SearchAndReplace($KY,"LA","Louisiana"); var text ME = SearchAndReplace($LA,"ME","Maine"); var text MD = SearchAndReplace($ME,"MD","Maryland"); var text MA = SearchAndReplace($MD,"MA","Massachusetts"); var text MI = SearchAndReplace($MA,"MI","Michigan"); var text MN = SearchAndReplace($MI,"MN","Minnesota"); var text MS = SearchAndReplace($MN,"MS","Mississippi"); var text MO = SearchAndReplace($MS,"MO","Missouri"); var text MT = SearchAndReplace($MO,"MT","Montana"); var text NE = SearchAndReplace($MT,"NE","Nebraska"); var text NV = SearchAndReplace($NE,"NV","Nevada"); var text NH = SearchAndReplace($NV,"NH","New Hampshire"); var text NJ = SearchAndReplace($NH,"NJ","New Jersey"); var text NM = SearchAndReplace($NJ,"NM","New Mexico"); var text NY = SearchAndReplace($NM,"NY","New York"); var text NC = SearchAndReplace($NY,"NC","North Carolina"); var text ND = SearchAndReplace($NC,"ND","North Dakota"); var text OH = SearchAndReplace($ND,"OH","Ohio"); var text OK = SearchAndReplace($OH,"OK","Oklahoma"); var text ORE = SearchAndReplace($OK,"OR","Oregon"); var text PA = SearchAndReplace($ORE,"PA","Pennsylvania"); var text RI = SearchAndReplace($PA,"RI","Rhode Island"); var text SC = SearchAndReplace($RI,"SC","South Carolina"); var text SD = SearchAndReplace($SC,"SD","South Dakota"); var text TN = SearchAndReplace($SD,"TN","Tennessee"); var text TX = SearchAndReplace($TN,"TX","Texas"); var text UT = SearchAndReplace($TX,"UT","Utah"); var text VT = SearchAndReplace($UT,"VT","Vermont"); var text VA = SearchAndReplace($VT,"VA","Virginia"); var text WA = SearchAndReplace($VA,"WA","Washington"); var text WV = SearchAndReplace($WA,"WV","West Virginia"); var text WI = SearchAndReplace($WV,"WI","Wisconsin"); var text WY = SearchAndReplace($WI,"WY","Wyoming"); $WY

     

3 Replies

Sort By
  • DougHenning1's avatar
    DougHenning1
    Community Manager
    11 days ago

    You could also use the "Case" statement:

    var text stateAbbr = [State];
Case(Upper($stateAbbr),
"AL", "Alabama",
"AK", "Alaska",
"AZ", "Arizona",
"AR", "Arkansas",
"CA", "California",
"CO", "Colorado",
"CT", "Connecticut",
"DE", "Delaware",
"DC", "District of Columbia",
"FL", "Florida",
"GA", "Georgia",
"HI", "Hawaii",
"ID", "Idaho",
"IL", "Illinois",
"IN", "Indiana",
"IA", "Iowa",
"KS", "Kansas",
"KY", "Kentucky",
"LA", "Louisiana",
"ME", "Maine",
"MD", "Maryland",
"MA", "Massachusetts",
"MI", "Michigan",
"MN", "Minnesota",
"MS", "Mississippi",
"MO", "Missouri",
"MT", "Montana",
"NE", "Nebraska",
"NV", "Nevada",
"NH", "New Hampshire",
"NJ", "New Jersey",
"NM", "New Mexico",
"NY", "New York",
"NC", "North Carolina",
"ND", "North Dakota",
"OH", "Ohio",
"OK", "Oklahoma",
"OR", "Oregon",
"PA", "Pennsylvania",
"RI", "Rhode Island",
"SC", "South Carolina",
"SD", "South Dakota",
"TN", "Tennessee",
"TX", "Texas",
"UT", "Utah",
"VT", "Vermont",
"VA", "Virginia",
"WA", "Washington",
"WV", "West Virginia",
"WI", "Wisconsin",
"WY", "Wyoming", 
$stateAbbr)

     

  • JimHarrison's avatar
    JimHarrison
    Qrew Champion
    11 days ago

    This works for the Dashboard filters. 

    var text StateAbbr = [State]; var text AL = SearchAndReplace($StateAbbr,"AL","Alabama"); var text AK = SearchAndReplace($AL,"AK","Alaska"); var text AZ = SearchAndReplace($AK,"AZ","Arizona"); var text AR = SearchAndReplace($AZ,"AR","Arkansas"); var text CA = SearchAndReplace($AR,"CA","California"); var text CO = SearchAndReplace($CA,"CO","Colorado"); var text CT = SearchAndReplace($CO,"CT","Connecticut"); var text DE = SearchAndReplace($CT,"DE","Delaware"); var text FL = SearchAndReplace($DE,"FL","Florida"); var text GA = SearchAndReplace($FL,"GA","Georgia"); var text HI = SearchAndReplace($GA,"HI","Hawaii"); var text ID = SearchAndReplace($HI,"ID","Idaho"); var text IL = SearchAndReplace($ID,"IL","Illinois"); var text IN = SearchAndReplace($IL,"IN","Indiana"); var text IA = SearchAndReplace($IN,"IA","Iowa"); var text KS = SearchAndReplace($IA,"KS","Kansas"); var text KY = SearchAndReplace($KS,"KY","Kentucky"); var text LA = SearchAndReplace($KY,"LA","Louisiana"); var text ME = SearchAndReplace($LA,"ME","Maine"); var text MD = SearchAndReplace($ME,"MD","Maryland"); var text MA = SearchAndReplace($MD,"MA","Massachusetts"); var text MI = SearchAndReplace($MA,"MI","Michigan"); var text MN = SearchAndReplace($MI,"MN","Minnesota"); var text MS = SearchAndReplace($MN,"MS","Mississippi"); var text MO = SearchAndReplace($MS,"MO","Missouri"); var text MT = SearchAndReplace($MO,"MT","Montana"); var text NE = SearchAndReplace($MT,"NE","Nebraska"); var text NV = SearchAndReplace($NE,"NV","Nevada"); var text NH = SearchAndReplace($NV,"NH","New Hampshire"); var text NJ = SearchAndReplace($NH,"NJ","New Jersey"); var text NM = SearchAndReplace($NJ,"NM","New Mexico"); var text NY = SearchAndReplace($NM,"NY","New York"); var text NC = SearchAndReplace($NY,"NC","North Carolina"); var text ND = SearchAndReplace($NC,"ND","North Dakota"); var text OH = SearchAndReplace($ND,"OH","Ohio"); var text OK = SearchAndReplace($OH,"OK","Oklahoma"); var text ORE = SearchAndReplace($OK,"OR","Oregon"); var text PA = SearchAndReplace($ORE,"PA","Pennsylvania"); var text RI = SearchAndReplace($PA,"RI","Rhode Island"); var text SC = SearchAndReplace($RI,"SC","South Carolina"); var text SD = SearchAndReplace($SC,"SD","South Dakota"); var text TN = SearchAndReplace($SD,"TN","Tennessee"); var text TX = SearchAndReplace($TN,"TX","Texas"); var text UT = SearchAndReplace($TX,"UT","Utah"); var text VT = SearchAndReplace($UT,"VT","Vermont"); var text VA = SearchAndReplace($VT,"VA","Virginia"); var text WA = SearchAndReplace($VA,"WA","Washington"); var text WV = SearchAndReplace($WA,"WV","West Virginia"); var text WI = SearchAndReplace($WV,"WI","Wisconsin"); var text WY = SearchAndReplace($WI,"WY","Wyoming"); $WY

     

  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    11 days ago

    .. and here is another one.  I forget offhand why I had to do the upper and lowercase thing but I think it had to do with inconsistent data entry when users typed in the state name and the capitalization was inconsistent.  But I see I did something different for the states which had two names.  I have a feeling I should have just use the two word name method for all the states.  

     

    var text StateRaw = Trim([Address: State/Region]);
    var text State = Upper(Left($StateRaw,1)) & Lower(NotLeft($StateRaw,1));

    If(
    $State="Alabama", "AL",
    $State="Alaska", "AK",
    $State="Arizona", "AZ",
    $State="Arkansas", "AR",
    $State="California", "CA",
    $State="Colorado", "CO",
    $State="Connecticut", "CT",
    $State="Delaware", "DE",
    Upper($State)="DISTRICT OF COLUMBIA", "DC",
    $State="Florida", "FL",
    $State="Georgia", "GA",
    $State="Hawaii", "HI",
    $State="Idaho", "ID",
    $State="Illinois", "IL",
    $State="Indiana", "IN",
    $State="Iowa", "IA",
    $State="Kansas", "KS",
    $State="Kentucky", "KY",
    $State="Louisiana", "LA",
    $State="Maine", "ME",
    $State="Maryland", "MD",
    $State="Massachusetts", "MA",
    $State="Michigan", "MI",
    $State="Minnesota", "MN",
    $State="Mississippi", "MS",
    $State="Missouri", "MO",
    $State="Montana", "MT",
    $State="Nebraska", "NE",
    $State="Nevada", "NV",
    Upper($State)="NEW HAMPSHIRE", "NH",
    Upper($State)="NEW JERSEY", "NJ",
    Upper($State)="NEW MEXICO", "NM",
    Upper($State)="NEW YORK", "NY",
    Upper($State)="NORTH CAROLINA", "NC",
    Upper($State)="NORTH DAKOTA", "ND",
    $State="Ohio", "OH",
    $State="Oklahoma", "OK",
    $State="Oregon", "OR",
    $State="Pennsylvania", "PA",
    $State="Rhode Island", "RI",
    Upper($State)="SOUTH CAROLINA", "SC",
    Upper($State)="SOUTH DAKOTA", "SD",
    $State="Tennessee", "TN",
    $State="Texas", "TX",
    $State="Utah", "UT",
    $State="Vermont", "VT",
    $State="Virginia", "VA",
    $State="Washington", "WA",
    Upper($State)="WEST VIRGINIA", "WV",
    $State="Wisconsin", "WI",
    $State="Wyoming", "WY", 
    UPPER($State))