Added 24 hours later: D Mitchell pointed out that CASE will do this too. I confess I had completely forgotten about CASE and Donald's completely right: For the particular problem that I claim to be solving here, CASE makes sense and my approach is, well, nutty. I'll leave the post up though because it does still illustrate another trick that we need in SmartSuite still until we get a FIND function: using large numbers of spaces to break the elements of a string apart and then grab the one you want. Works for example for breaking up full names.
If you're looking for a way to convert state names to abbreviations, skip this and read Donald's response, below. 😉
.
I need to send another party a list of addresses out of our SmartSuite solution. The validated addresses in SmartSuite show full state names (Texas, North Carolina, etc) but the other party to whom I need to send this data wants two-letter state abbreviations. Easy if we had a SWITCH() function or even a FIND function, but we don't. I can think of other ways to solve this but here's one. The field State Long Name is the long state name I've pulled out of the validated address field. Here's the formula for the abbreviation:
LEFT(TRIM(RIGHT(
REPLACE(REPLACE("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"," ",""),CONCAT(REPLACE([State Long Name]," ",""),"="),CONCAT(
" ",
" ",
" ",
" ",
" ",
" ",
" ",
" ",
" ",
" ",
" ",
" ",
" ",
" "
)
)
,700)),2)
You're welcome to copy and paste that formula. Other than the reference to the [State Long Name] in the middle, it's a self-contained formula.
.
Gist of it is fairly simple: The key string consists of state long name and state abbreviation pairs, with equals sign between long name and abbreviation, and otherwise separated by commas. Like this:
New York=NY, North Carolina=NC, North Dakota=ND, Ohio=OH, Oklahoma=OK
The long name (pulled from the original source field, which could be the validated address field) plus the equals sign I expect to follow it is replaced by a string of spaces as long as the original list of states and abbreviations: I use 700 spaces in the real formula, but just 70 for this example. So if I was looking for North Dakota, that example string above would get changed to
New York=NY, North Carolina=NC, ND, Ohio=OH, Oklahoma=OK
except that I only put 70 spaces in there, not 700, to make it easier to see in this short example. Now I just take the RIGHT 70 characters, which looks something like this
ND, Ohio=OH, Oklahoma=OK
TRIM it, and take the left LEFT first two characters. So long state name North Dakota returns "ND". Q.E.D.
.
This trick of replacing a value in the original string with a very long list of spaces, then getting a part of the resulting string and trimming it, is a neat trick that can be used to parse out first, middle and last names and to solve other similar problems. NOT good if the data to be parsed is really long.
No doubt other ways to solve this problem. But this one works. If you want to add in the Canadian provinces, be sure to increase the number spaces that you use in the key REPLACE part of the formula!