remove/ keep/ split just last 2 LETTERS in a string

[JLMToth]

Hello,

I cannot get my formulas to work, and everything i have found only seems to work for numbers which does me no good.

I have a field with cities and states in it and want to split it to 2 columns one with city one with state. but the problem is some have a period between then, some have a comma and some have nothing between the 2. some Cities have a period in them already (ie. St. Louis) and some are multiple words (IE Cuyahoga Falls) the only common thing is that the last 2 letters are always the state. so i tried trim right/left to return the value without the last 2 and the value that is only the last 2... but have had no success. as soon as i try to put in the length of how many characters to return the formula stops working. like it doesnt want me to put a number in the length part.

[PhilipMannering · Accepted solution]

Try Regex Tool > Parse,

(.*)(\u\u)

[estherb47]

Hi @JLMToth 

Try the Length function to determine the length of the whole field, then subtract 2 from that answer.

So, Right([Field],length([Field])-2)

Let me know if that helps

Cheers!

Esther

[JLMToth]

that was what i thought it should have been but it took the first 2 letters off the city name instead so if it was Cuyahoga Falls OH what was returned was yagoha Falls OH.  i tried going from the left then  (exact same formula but left instead if right) and it just kept erroring out... i am starting to wonder if there isnt a problem with my data

[JLMToth]

This did exactly what we needed and in one step! (better than what i was trying to do to start with) Thank you so much!