OR my state abbreviation breaks Quickbase filters. Does anyone have a formula to share?
Solved
Looking for State abbreviation to full spelling formula
+18Best answer by MarkShnierYou
.. 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))
+24.. 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))
+18This 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
+6You 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)
Sign up
Already have an account? Login
Login to the community
Login with Quickbase US – quickbase.com Login with Quickbase EU – quickbase.eu
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.