+ Start a Discussion
SLockardSLockard 

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)),
	if(UPPER(Account.BillingState) = 'ALASKA'||UPPER(Account.BillingState) = 'MISSOURI',
		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!

Best Answer chosen by Admin (Salesforce Developers) 
SLockardSLockard

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))))))))))),
'')

 

All Answers

SLockardSLockard

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 pppsss ppp
Can you explain the above requiremnt as well as solution?