Forum Discussion

Terp's avatar
Terp
Advocate III
4 years ago
Solved

Calculated Column: Return Two (2) Characters After Comma

I was trying to figure out how to do a reverse concantination, per se, and got close...but can't get it right. 🙂   I have a long string of city/MSA information and I want to join this city table t...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Terp , On Top of your formula use left(<Current formula>,2)

    I think that might help

MPetramalo214's avatar
MPetramalo214
Helper I
4 years ago

Can you try adding the LEFT function to what you already have? 

St Abbrv = 
LEFT (
    RIGHT ( [City/MSA/MD], LEN ( [City/MSA/MD] ) - FIND ( ", ", [City/MSA/MD] ) - 1 ),
    2
)

 This should get you the two characters on the left, of the text to the right of the comma. 

 

If you have a static number of states that an entry can be at most (ie. no more than two/three states), you could probably add a conditional to check the third digit from the left, and if its a "-" then you have two states, and return the first 5 characters, otherwise return the first 2.