1

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?

2
  • 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

1 Answer 1

0

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)

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.