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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.