I have column in sharepoint 2010 list which has value like http://mysitecollection/sites/SPDev/Arizona/Lighthouse.jpg

I want the value in calculated column should be Arizona. Can anyone let me know the formula for this manipulation?


Create a calculated column and set the return type to Single line of text.

If the URL path until Arizona is constant, then add the below formula

=LEFT(RIGHT([Column Name],LEN([Column Name])-36),FIND("/",RIGHT([Column Name],LEN([Column Name])-36))-1)

Replace the column name and Number "36" based on the number of characters before the text "Arizona".


If the sitename before the Statename is fixed you can get everything after SPDev/ with:

=MID( [Col] , FIND("SPDev/", [Col] )+6 , 255)

+6 is the length of SPDev/

You then have to get the string length up to the first / (after the Statename)

=FIND( "/" , MID( [Col] ,FIND("SPDev/", [Col] )+6,255) )-1

All together in one Formula:

=MID( [Col] ,FIND("SPDev/", [Col] )+6,FIND("/",MID( [Col] ,FIND("SPDev/", [Col] )+6,255))-1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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