You can not change the field itself to a calculation. You can either create a new calculation field, or you can use an auto-entry with calculated value.
BTW, you may want to possibly store this info in a states table and then use a lookup.
I changed the field to a calculation field and input the following substitute formula.
Hm, this is not quite clear. If you change a text or a number field to a calculation field, then you usually loose the data there. (You get a warning message before.) I guess your field stays a text field and you used an Auto-Enter calculation?
Two things come into my mind:
1. If you have already data in a field the Auto-Enter option sometimes is confusing. What was entered gets changed usually only when the data in the field got changed AND the checkbox "Do not replace existing value..." is unchecked. You can try creating a new "real" calculation field and see what happens. (This way you also make sure that you don't loose any data in the original State field.)
2. The Substitute function is case sensitive(!)
Ok. I think I'm beginning to understand.
(I had previously changed an address field which had text in it to a calculation field which used this formula: Substitute ( Trim ( Substitute ( Address ; [ " " ; "§" ] ; [ ¶ ; " " ] ) ) ; [ " " ; ¶ ] ; [ "§" ; " " ] ) and the function worked and altered the text in that field. That was what I was trying to do here since this is really a formatting kind of issue for printing labels).
Taking the advice, I added another field (State Abbreviations), changed the original State field back to a text field, and put the Substitute formula from the original post in the newly created State Abbreviation field. Now the state Abbreviation field has the states fully spelled out in it. So, it clearly referenced the right field, but it did not perform the text substitution. I must have something wrong in the formula, but I cannot find it.
Thanks for helping this newbie!
I think I found it! The original source data was traditionally capitalized: Alabama. I had formatted the text in all fields to All CAPS since that's what the USPS prefers for shipping labels. I edited the formula to the original data's capitalization and it worked! I knew that it was case specific, but did not realize that it would not respect applied formatting.
Even when placed in the original field, the substitution worked correctly!
Thanks for you input. I learned a lot from this little adventure.
Now the state Abbreviation field has the states fully spelled out in it. So, it clearly referenced the right field, but it did not perform the text substitution.
If you wanted states fully spelled out, you should have reversed the order:
Substitute ( State; [ "AL" ; "ALABAMA" ] ; …
However, it's more appropriate to keep the states in a table. You don't need any calculation here - enter the abbreviation in the StateAbbreviation field, and get the full name directly from the related record.
BTW, that Trim formula looks familiar … :smileyhappy:
I didn't make the connection with the trim formula. It saved me! I could not figure it out, but it works like a charm and seeing it helped me figure out a bit of the formula syntax I had misunderstood.