Good day,
I have a rather lengthy formula in a calculated column, with alot of
concatinated IF statements, but running into a bit of a snag. The list is
Staff Contact List and includes such things as name phone, fax and Office
(which is a drop down list). For a Mailing Address column I set up a
calculated column and a number of concatinated IF statements. The IF
statement work fine, but when I include the actual street address, city and
postal code it seems like it puts the formula field beyond a certain
character limit. If I just include the street address, for instance, it
works fine.
Can anyone tell me if there is a character limitation within a formula
field, and if it is configurable (can be increased) somehow?
If not, perhaps someone can enlighten me on a better way to do what I am
attempting: Below is a copy of what I have right now in the formula field
of my calculated column:
=IF(Office="Antigonish",
"219 Main Street"&CHAR(10)&
"Antigonish"&CHAR(10)&
"Nova Scotia"&CHAR(10)&
"B2G 2C1","")
&IF(Office="Baddeck",
"P.O. Box 359"&CHAR(10)&
"486 Chebucto Street"&CHAR(10)&
"Baddeck"&CHAR(10)&
"Nova Scotia"&CHAR(10)&
"B0E 1B0","")
&IF(Office="Cheticamp",
"P.O. Box 670"&CHAR(10)&
"Cheticamp"&CHAR(10)&
"Nova Scotia"&CHAR(10)&
"B0E 1H0","")
&IF(Office="Elmsdale",
"138 Highway 214"&CHAR(10)&
"Unit 1"&CHAR(10)&
"Elmsdale"&CHAR(10)&
"Nova Scotia"&CHAR(10)&
"B2S 1G6","")
&IF(Office="Glace Bay",
"5 Reserve Street"&CHAR(10)&
"Glace Bay"&CHAR(10)&
"Nova Scotia"&CHAR(10)&
"B1A 4V6","")
&IF(Office="New Glasgow",
"509 East River Road"&CHAR(10)&
"New Glasgow"&CHAR(10)&
"Nova Scotia"&CHAR(10)&
"B2H 3R5","")
&IF(Office="New Minas",
"8759 Commercial Street"&CHAR(10)&
"Unit 2A"&CHAR(10)&
"New Minas"&CHAR(10)&
"Nova Scotia"&CHAR(10)&
"B4N 3C4","")
&IF(Office="North Sydney",
"92 Pierce Street"&CHAR(10)&
"North Sydney"&CHAR(10)&
"Nova Scotia"&CHAR(10)&
"B2A 1N8","")
&IF(Office="Port Hawkesbury",
"326 Granville Street"&CHAR(10)&
"Port Hawkesbury"&CHAR(10)&
"Nova Scotia"&CHAR(10)&
"B9A 2M6","")
&IF(Office="New Glasgow",
"509 East River Road"&CHAR(10)&
"New Glasgow"&CHAR(10)&
"Nova Scotia"&CHAR(10)&
"B2H 3R5","")
&IF(Office="Sydney",
"64 Brookland Street"&CHAR(10)&
"2nd Floor"&CHAR(10)&
"Sydney"&CHAR(10)&
"Nova Scotia"&CHAR(10)&
"B1P 5B2","")
&IF(Office="Sydney River",
"P.O. Box 1507"&CHAR(10)&
"1053 Kings Road"&CHAR(10)&
"Sydney River"&CHAR(10)&
"Nova Scotia"&CHAR(10)&
"B1P 6R7","")
&IF(Office="Tatamagouche",
"P.O. Box 220"&CHAR(10)&
"38 Maple Street"&CHAR(10)&
"Tatamagouche"&CHAR(10)&
"Nova Scotia"&CHAR(10)&
"B0K 1V0","")
&IF(Office="Truro",
"956 Prince Street"&CHAR(10)&
"Truro"&CHAR(10)&
"Nova Scotia"&CHAR(10)&
"B2N 1H8","")
&IF(Office="Whycocomagh",
"P.O. Box 100"&CHAR(10)&
"9492 Trans Canada Highway 125"&CHAR(10)&
"Whycocomagh"&CHAR(10)&
"Nova Scotia"&CHAR(10)&
"B0E 3M0","")
&IF(Office="Financial Services",
"64 Brookland Street"&CHAR(10)&
"2nd Floor"&CHAR(10)&
"Sydney"&CHAR(10)&
"Nova Scotia"&CHAR(10)&
"B1P 5B2","")
Thanks for any help you can provide!
- Joe