0

I was not able to get the answers from Get parent folder path from file path using cell formula to work with my data.

Using the below, only one part of my path gets trimmed.

=LEFT(A1,FIND("?",SUBSTITUTE(A1,"\","?",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

I have /stack/over/flow/today/is/friday in A1, above formulas will give me /stack/over/flow/today/is/

Result i want is /stack or stack/ or just stack from my path.

Any VBA,functions or formulas are welcome.

Community
  • 1
  • 1
Jonnyboi
  • 505
  • 5
  • 19

3 Answers3

2

This will return the first text between the first 2 "/"

=TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",999)),998,999))

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 1
    @Jonnyboi there is a check mark by the answer that will mark this as the correct answer, it is something only you can do. Please click it. Also I noticed that none of your questions had correct answers marked you probably should go back and fix that. – Scott Craner May 20 '16 at 19:59
2

UDF ( User Defined Function) you can use the function as follow while your path is in A1.

=GetFirstFolder(A1, "/", 1) will return: Stack 

Function GetFirstFolder(sPath As String, sDiv As String, Optional lOrder As Long = 0) As String
        Application.Volatile 'This will make your function recalculate wherever the function is put.

        GetFirstFolder = Split(sPath, sDiv)(lOrder)
End Function

Hope this helps. hadi

Hadi
  • 184
  • 10
1

And a slightly different approach with a shorter formula:

=MID(A1,2,FIND("/",A1,2)-2)

The 2 for start skips over the first /

The -2 in the length results in not returning the second /

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60