Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
SLockard

# Formula to set field to abbreviation of state

Hi, I am trying to make a forumla on the Contact which takes its accounts billing state and correctly sets it to the proper abbreviation. Here is my formula:

```if(Account.BillingState <> null,
UPPER(LEFT(Account.BillingState,1))+
if(LEN(Account.BillingState) = 2,
UPPER(RIGHT(Account.BillingState,1)),
if(CONTAINS(Account.BillingState, ' '),
UPPER(LEFT(RIGHT(Account.BillingState, FIND(' ', Account.BillingState)), 1)),
if(BEGINS(UPPER(Account.BillingState), 'T')||UPPER(Account.BillingState) = 'NEVADA'||UPPER(Account.BillingState) = 'MISSISSIPPI'||UPPER(Account.BillingState) = 'MINNESOTA',
UPPER(LEFT(RIGHT(Account.BillingState, 2), 1)),
if(UPPER(Account.BillingState) = 'ARIZONA'||UPPER(Account.BillingState) = 'MONTANA',
UPPER(LEFT(RIGHT(Account.BillingState, 3), 1)),
UPPER(LEFT(RIGHT(Account.BillingState, 4),1)),
if(BEGINS(UPPER(Account.BillingState),'W')||BEGINS(UPPER(Account.BillingState),'A')||BEGINS(UPPER(Account.BillingState),'N')||BEGINS(UPPER(Account.BillingState),'U')||BEGINS(UPPER(Account.BillingState),'O')||BEGINS(UPPER(Account.BillingState),'D')||BEGINS(UPPER(Account.BillingState),'F')||BEGINS(UPPER(Account.BillingState), 'ID')||BEGINS(UPPER(Account.BillingState), 'IN')||BEGINS(UPPER(Account.BillingState), 'IL'),
UPPER(LEFT(RIGHT(Account.BillingState, 1), 1)),
if(BEGINS(UPPER(Account.BillingState),'V')||BEGINS(UPPER(Account.BillingState),'P')||BEGINS(UPPER(Account.BillingState),'L')||BEGINS(UPPER(Account.BillingState),'K')||BEGINS(UPPER(Account.BillingState),'G')||BEGINS(UPPER(Account.BillingState),'H')||BEGINS(UPPER(Account.BillingState),'I'),
UPPER(RIGHT(Account.BillingState, LEN(Account.BillingState)-1)),
if(BEGINS(UPPER(Account.BillingState), 'C'),
if(LEN(Account.BillingState) < 11,
UPPER(LEFT(RIGHT(Account.BillingState, 1), 1)),
UPPER(RIGHT(Account.BillingState, LEN(Account.BillingState)-1))),
if(BEGINS(UPPER(Account.BillingState), 'MAI')||BEGINS(UPPER(Account.BillingState), 'MAR'),
UPPER(RIGHT(Account.BillingState, LEN(Account.BillingState)-1)),
UPPER(LEFT(RIGHT(Account.BillingState, 1), 1))))))))))),
'')```

The logic I used is a little weird because of the 5000 char limit, but if you see any issues with it let me know because it's not working correctly.

Thanks!

SLockard

I figured it out, I realized I wasn't using the LEFT() and RIGHT() functions properly. Here is the correct code if anyone else finds it interesting..

`if(Account.BillingState <> null,    UPPER(LEFT(Account.BillingState,1))+    if(LEN(Account.BillingState) = 2,        UPPER(RIGHT(Account.BillingState,1)),    if(CONTAINS(Account.BillingState, ' '),        UPPER(RIGHT(LEFT(Account.BillingState, FIND(' ', Account.BillingState)+1), 1)),    if(BEGINS(UPPER(Account.BillingState), 'T')||UPPER(Account.BillingState) = 'NEVADA'||UPPER(Account.BillingState) = 'MISSISSIPPI'||UPPER(Account.BillingState) = 'MINNESOTA',        UPPER(RIGHT(LEFT(Account.BillingState, 3), 1)),    if(UPPER(Account.BillingState) = 'ARIZONA'||UPPER(Account.BillingState) = 'MONTANA',        UPPER(RIGHT(LEFT(Account.BillingState, 4), 1)),    if(UPPER(Account.BillingState) = 'ALASKA'||UPPER(Account.BillingState) = 'MISSOURI',        UPPER(RIGHT(LEFT(Account.BillingState, 5),1)),    if(BEGINS(UPPER(Account.BillingState),'W')||BEGINS(UPPER(Account.BillingState),'A')||BEGINS(UPPER(Account.BillingState),'N')||BEGINS(UPPER(Account.BillingState),'U')||BEGINS(UPPER(Account.BillingState),'O')||BEGINS(UPPER(Account.BillingState),'D')||BEGINS(UPPER(Account.BillingState),'F')||BEGINS(UPPER(Account.BillingState), 'ID')||BEGINS(UPPER(Account.BillingState), 'IN')||BEGINS(UPPER(Account.BillingState), 'IL'),        UPPER(RIGHT(LEFT(Account.BillingState, 2), 1)),    if(BEGINS(UPPER(Account.BillingState),'V')||BEGINS(UPPER(Account.BillingState),'P')||BEGINS(UPPER(Account.BillingState),'L')||BEGINS(UPPER(Account.BillingState),'K')||BEGINS(UPPER(Account.BillingState),'G')||BEGINS(UPPER(Account.BillingState),'H')||BEGINS(UPPER(Account.BillingState),'I'),        UPPER(RIGHT(Account.BillingState, 1)),    if(BEGINS(UPPER(Account.BillingState), 'C'),        if(LEN(Account.BillingState) < 11,            UPPER(RIGHT(LEFT(Account.BillingState, 2), 1)),        UPPER(RIGHT(Account.BillingState, 1))),    if(BEGINS(UPPER(Account.BillingState), 'MAI')||BEGINS(UPPER(Account.BillingState), 'MAR'),        UPPER(RIGHT(Account.BillingState, 1)),    UPPER(RIGHT(LEFT(Account.BillingState, 2), 1))))))))))),'')`

SLockard

I figured it out, I realized I wasn't using the LEFT() and RIGHT() functions properly. Here is the correct code if anyone else finds it interesting..

`if(Account.BillingState <> null,    UPPER(LEFT(Account.BillingState,1))+    if(LEN(Account.BillingState) = 2,        UPPER(RIGHT(Account.BillingState,1)),    if(CONTAINS(Account.BillingState, ' '),        UPPER(RIGHT(LEFT(Account.BillingState, FIND(' ', Account.BillingState)+1), 1)),    if(BEGINS(UPPER(Account.BillingState), 'T')||UPPER(Account.BillingState) = 'NEVADA'||UPPER(Account.BillingState) = 'MISSISSIPPI'||UPPER(Account.BillingState) = 'MINNESOTA',        UPPER(RIGHT(LEFT(Account.BillingState, 3), 1)),    if(UPPER(Account.BillingState) = 'ARIZONA'||UPPER(Account.BillingState) = 'MONTANA',        UPPER(RIGHT(LEFT(Account.BillingState, 4), 1)),    if(UPPER(Account.BillingState) = 'ALASKA'||UPPER(Account.BillingState) = 'MISSOURI',        UPPER(RIGHT(LEFT(Account.BillingState, 5),1)),    if(BEGINS(UPPER(Account.BillingState),'W')||BEGINS(UPPER(Account.BillingState),'A')||BEGINS(UPPER(Account.BillingState),'N')||BEGINS(UPPER(Account.BillingState),'U')||BEGINS(UPPER(Account.BillingState),'O')||BEGINS(UPPER(Account.BillingState),'D')||BEGINS(UPPER(Account.BillingState),'F')||BEGINS(UPPER(Account.BillingState), 'ID')||BEGINS(UPPER(Account.BillingState), 'IN')||BEGINS(UPPER(Account.BillingState), 'IL'),        UPPER(RIGHT(LEFT(Account.BillingState, 2), 1)),    if(BEGINS(UPPER(Account.BillingState),'V')||BEGINS(UPPER(Account.BillingState),'P')||BEGINS(UPPER(Account.BillingState),'L')||BEGINS(UPPER(Account.BillingState),'K')||BEGINS(UPPER(Account.BillingState),'G')||BEGINS(UPPER(Account.BillingState),'H')||BEGINS(UPPER(Account.BillingState),'I'),        UPPER(RIGHT(Account.BillingState, 1)),    if(BEGINS(UPPER(Account.BillingState), 'C'),        if(LEN(Account.BillingState) < 11,            UPPER(RIGHT(LEFT(Account.BillingState, 2), 1)),        UPPER(RIGHT(Account.BillingState, 1))),    if(BEGINS(UPPER(Account.BillingState), 'MAI')||BEGINS(UPPER(Account.BillingState), 'MAR'),        UPPER(RIGHT(Account.BillingState, 1)),    UPPER(RIGHT(LEFT(Account.BillingState, 2), 1))))))))))),'')`

This was selected as the best answer
sss ppp
Can you explain the above requiremnt as well as solution?