# Calculated Column to extract text within file path

Due to SP limitations in workflows for extracting text strings, I'm looking for another way to skin the cat.

If I have a single-line text column that has a file path like:

``````C:\folder1\folder2\StringIWantToExtract\anothertextstringofunknownlength.html
``````

I know the string "`C:\folder1\folder2\`" will always be the same, but the `StringIWantToExtract` will be an unknown length, as will the string after the last "\".

I would think there would be a way to tell a formula to start the extraction after the first, known part of the string, and then maybe just erase the text that includes and follows the last "/" in the string.

Ideas?

• What do you want to do? Store it somewhere? Display it in a View? Jul 28, 2015 at 16:28
• i was basically extracting it with the calc column because i couldn't do the same task in a workflow
– Carp
Jul 28, 2015 at 22:57

I received some help from a friend, and from some posters on the MrExcel board. The formulas offered up do work for this task

First

``````=MID(A1,20,FIND("\",MID(A1,20,255))-1)
``````

where the character count of "20" is the set number of characters in the front, known string +1.

another formula for doing it relies on the three \ characters and looks like this

``````=MID(A1,FIND("\",A1,FIND("\",A1,FIND("\",A1)+1)+1)+1,FIND("\",A1,FIND("\",A1,FIND("\",A1,FIND("\",A1)+1)+1)+1)-FIND("\",A1,FIND("\",A1,FIND("\",A1)+1)+1)-1)
``````