How to have string between ":" calculate column?

Question

I'm working on SharePoint Enterprise 2013. I have a column call "CountryWF" wich shows Country:state:city. I need a calculate column to shows between ":" which is State. I made some columns and each returned different but not State.

For example the column is: United States:Virginia:Falls church
I use the code bellow:

 (MID([CountryWF], FIND(":", [CountryWF])+1, LEN([CountryWF]))    

and the out put will be: Virginia:Falls church but I need only Virginia. I also tried this code:

 =MID([CountryWF], SEARCH(":",[CountryWF],0)), SEARCH(":", [CountryWF], 0))​

but it contain syntax error and didn't work. If any one could help me I appreciate.

Answer 1

One way to achieve this - Assuming data is in the correct format in all cases

=LEFT((MID(CountryWF,FIND(":",CountryWF)+1,LEN(CountryWF))), FIND(":",(MID(CountryWF,FIND(":",CountryWF)+1,LEN(CountryWF))))-1)