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

Terp's avatar
Terp
Advocate III
4 years ago

Your genius is equally impressive, thanks! 🙂

 

'Left (right...' just didn't seem right. Thank you both for the help...never would have stumbbled on that one.

MPetramalo214's avatar
MPetramalo214
Helper I
4 years ago

It does seem like a strange combination.. I went ahead and threw together a mockup of how you could go about getting multiple states.  This will only work if you have a static number of states; if its dynamic, then a bunch of nested if statements probably isn't efficient enough.

St Abbrv = 
var rightOfComma = RIGHT([Column2], LEN([Column2]) - FIND(", ", [Column2])-1)

var thirdChar = RIGHT(LEFT(rightOfComma, 3), 1)

return
IF(
    thirdChar = "-",
    LEFT(rightOfComma, 5),
    LEFT(rightOfComma, 2)
)