Can't get the IF formula to work for State abbreviations

[Eric_McQuesten]

Hey everyone,

I am trying to create a formula that allows me to abbreviate the States,but for some reason it won’t let me. I believe I am doing it correctly. Within my table I have a lookup table where applicants can select their state but I can’t get the formula to abbreviate the field.

I’ve attached an image for reference.

[Chris-T]

Hi Eric. Do you need the non-abbreviated state string for anything? Or are you only going to use the abbreviation. If not, I would just save yourself the hassle and let the user select an abbreviation.

Otherwise, it looks like your long State field is a lookup. I’m not sure if you need to force it into a string using " &’’ " or CONCATENATE, but you need quotes around the state string (like ‘Maine’).

You also just might want a table of State, Abbrev pairs so that you can do a lookup and not some terrible set of IF statements (or SWITCH which you should use if you go that route).

Let me know if you have any questions.

Let me know if that works.

[Eric_McQuesten]

Hi Chris,

Thanks for the response. I am using both the full state name and the abbreviations of each state on the site I am pushing the data to. Ideally, I’d like to automatically generate the abbreviated state name inside my table when someone selects their state.

You mentioned a table of State/Abbreviation pairs. How would I go about creating that?

Thanks in advance for your help.

-Eric

[Chris-T]

Hey Eric, I have a question first. It looks like your field {State (from State Ref)} is a lookup field. Could you reply with a screen shot of where that comes from? What is the “State Ref” table?

To make the state/abbrev table:

The table I was referring to would have at least two fields, but could have more if there was other info about the state you needed to track. The primary field would be the long state name and there would need to be another field to hold the abbrev. Then, in your original table where the user selects a state, your state selector field would be a link field (not allowing multiple selections) to the state/abbrev table. You would add a new lookup field right there in that table that pulls the abbrev field in from the state/abbrev table via the link.