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.

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