The state address field displays the full name of the state. I would like to get the two letter abbreviation (i.e. "MI" instead of "Michigan"). Besides creating my own lookup table, is there another way?

Since I spent a not-insignificant amount of time on this, I'd like to prevent others from having to doing the same (though it was a fun exercising in trying to remember my state abbreviations!): If ( [Address: State/Region]="Alabama", "AL", [Address: State/Region]="Alaska", "AK", [Address: State/Region]="Arizona", "AZ", [Address: State/Region]="Arkansas", "AR", [Address: State/Region]="California", "CA", [Address: State/Region]="Colorado", "CO", [Address: State/Region]="Connecticut", "CT", [Address: State/Region]="Delaware", "DE", [Address: State/Region]="Florida", "FL", [Address: State/Region]="Georgia", "GA", [Address: State/Region]="Hawaii", "HI", [Address: State/Region]="Idaho", "ID", [Address: State/Region]="Illinois", "IL", [Address: State/Region]="Indiana", "IN", [Address: State/Region]="Iowa", "IA", [Address: State/Region]="Kansas", "KS", [Address: State/Region]="Kentucky", "KY", [Address: State/Region]="Louisiana", "LA", [Address: State/Region]="Maine", "ME", [Address: State/Region]="Maryland", "MD", [Address: State/Region]="Massachusetts", "MA", [Address: State/Region]="Michigan", "MI", [Address: State/Region]="Minnesota", "MN", [Address: State/Region]="Mississippi", "MS", [Address: State/Region]="Missouri", "MO", [Address: State/Region]="Montana", "MT", [Address: State/Region]="Nebraska", "NE", [Address: State/Region]="Nevada", "NV", [Address: State/Region]="New Hampshire", "NH", [Address: State/Region]="New Jersey", "NJ", [Address: State/Region]="New Mexico", "NM", [Address: State/Region]="New York", "NY", [Address: State/Region]="North Carolina", "NC", [Address: State/Region]="North Dakota", "ND", [Address: State/Region]="Ohio", "OH", [Address: State/Region]="Oklahoma", "OK", [Address: State/Region]="Oregon", "OR", [Address: State/Region]="Pennsylvania", "PA", [Address: State/Region]="Rhode Island", "RI", [Address: State/Region]="South Carolina", "SC", [Address: State/Region]="South Dakota", "SD", [Address: State/Region]="Tennessee", "TN", [Address: State/Region]="Texas", "TX", [Address: State/Region]="Utah", "UT", [Address: State/Region]="Vermont", "VT", [Address: State/Region]="Virginia", "VA", [Address: State/Region]="Washington", "WA", [Address: State/Region]="West Virginia", "WV", [Address: State/Region]="Wisconsin", "WI", [Address: State/Region]="Wyoming", "WY" )

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

yes, my client complained about that very thing. There should be an option to configure the address field to abbreviate US address States, I suppose.

Possible ways: 1. If you use a standard field "Address", just right click on the field and choose: "Edit the field properties for this field". You will see "Address field options", where you can find "States" . So , just change the full name of these states to the abbreviation, like New York - NY, California - CA. 2.Second possible way- write a formula. Add a new Text-formula field . Call it "Abb" If ( [State] = "New York", "NY"; ....) And write states what you need. And in the future, just use this field in your reports. Have a good one!

Thanks. I am using the standard Address field. The problem with Solution #1 is that when you search for an address, and select one from the auto-suggestions (a very handy feature), the state field is left blank. Quickbase seems to require that the state options be the complete, spelled-out version of the state names in order for this to work. I guess if I like that feature enough I'm forced to do option 2.

Converting state address field into two letter abbreviation

17 Replies

MatthewMatthew3
Qrew Cadet
8 years ago
Agreed
JohnBrindamour
Qrew Member
8 years ago
Thanks guys, very helpful. I ended up converting the state/region to upper and used Contains within an IF. Maybe that's possible with Case but I couldn't seem to get it to work.

If (
Contains(Upper([Ship To: State/Region]),"ALABAMA"), "AL",
Contains(Upper([Ship To: State/Region]),"ALASKA"), "AK",
Contains(Upper([Ship To: State/Region]),"ARIZONA"), "AZ",
Contains(Upper([Ship To: State/Region]),"ARKANSAS"), "AR",.........etc.
Lee_SGilmore
Qrew Cadet
7 years ago
Just in case someone wants the reverse, here it is.

Case([State],
"AL", "Alabama",
"AK", "Alaska",
"AZ", "Arizona",
"AR", "Arkansas",
"CA", "California",
"CO", "Colorado",
"CT", "Connecticut",
"DE", "Delaware",
"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")
AmberPolston
Qrew Cadet
7 years ago
Is there a way to take the formula field & automatically change [Address: State/Region]?

The formulas above changed the [Address: State/Region] to the format I needed, but I have over 10k records where the [Address: State/Region] fields needs to be changed to the formula field.

Forum Discussion

Converting state address field into two letter abbreviation

17 Replies

Related Content

Address field not working

Looking for State abbreviation to full spelling formula

Converting delimited text into checkboxes

Address Field - Set default value?

Convert timestamp to Date/Time